If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Summing Just the Visible Cells
If the auto-filter is engaged, is there a way to quickly
sum just the visible cells ? Thanks in advance. |
#2
|
|||
|
|||
Summing Just the Visible Cells
Hi!
The quickest (but ephemeral) way is to read it in the Status Bar (bottom right). Select the column you want to total and read off the total of the visible items. Works for count, average, max etc. Alf --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Summing Just the Visible Cells
Hi
use SUBTOTAL for this. e.g. =SUBTOTAL(9,A1:A10) -- Regards Frank Kabel Frankfurt, Germany "CARL" schrieb im Newsbeitrag ... If the auto-filter is engaged, is there a way to quickly sum just the visible cells ? Thanks in advance. |
#4
|
|||
|
|||
Summing Just the Visible Cells
Frank,
When I use subtotal, it sums all the cells not just the visible ones. I will admit, I am not using auto-filter, but have manually hidden some rows. Thx. lindasf --- Message posted from http://www.ExcelForum.com/ |
#5
|
|||
|
|||
Summing Just the Visible Cells
Hi
if you have Excel 2003 use =SUBTOTAL(109,A1:A100) Before that version you'll need VBA to check the hidden state within a user defined function -- Regards Frank Kabel Frankfurt, Germany Frank, When I use subtotal, it sums all the cells not just the visible ones. I will admit, I am not using auto-filter, but have manually hidden some rows. Thx. lindasf --- Message posted from http://www.ExcelForum.com/ |
#6
|
|||
|
|||
Summing Just the Visible Cells
You could use a User Defined Function like:
Option Explicit Function sumVisible(rng As Range) As Double Application.Volatile Dim mySum As Double Dim myCell As Range mySum = 0 For Each myCell In rng.Cells With myCell If .EntireRow.Hidden _ Or .EntireColumn.Hidden _ Or Application.IsNumber(.Value) = False Then 'do nothing Else mySum = mySum + .Value End If End With Next myCell sumVisible = mySum End Function If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ======= Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Type =sumVisible(A1:b9) But be a little careful with this kind of function. It updates each time excel recalculates. So if you hide some rows/columns and excel doesn't recalc, then your results may be off--until the next recalc. You can force a recalc by: tools|options|calculate tab|click the "Calc now" button (or just hit F9) "lindasf " wrote: Frank, When I use subtotal, it sums all the cells not just the visible ones. I will admit, I am not using auto-filter, but have manually hidden some rows. Thx. lindasf --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#7
|
|||
|
|||
Summing Just the Visible Cells
If you have Excel 2003, you can use =SUBTOTAL(109,A1:A100) which will add up
VISIBLE cells only. "Dave Peterson" wrote in message ... You could use a User Defined Function like: Option Explicit Function sumVisible(rng As Range) As Double Application.Volatile Dim mySum As Double Dim myCell As Range mySum = 0 For Each myCell In rng.Cells With myCell If .EntireRow.Hidden _ Or .EntireColumn.Hidden _ Or Application.IsNumber(.Value) = False Then 'do nothing Else mySum = mySum + .Value End If End With Next myCell sumVisible = mySum End Function If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ======= Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Type =sumVisible(A1:b9) But be a little careful with this kind of function. It updates each time excel recalculates. So if you hide some rows/columns and excel doesn't recalc, then your results may be off--until the next recalc. You can force a recalc by: tools|options|calculate tab|click the "Calc now" button (or just hit F9) "lindasf " wrote: Frank, When I use subtotal, it sums all the cells not just the visible ones. I will admit, I am not using auto-filter, but have manually hidden some rows. Thx. lindasf --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
SUBTOTAL Second Count / sub-count of Filtered Visible Cells | QTE | Worksheet Functions | 3 | July 21st, 2004 11:50 PM |
Subtotal: Min / Max Offset on Filtered Visible Cells? | QTE | Worksheet Functions | 4 | July 5th, 2004 09:44 PM |
Help summing only certain cells | Russ H | Worksheet Functions | 2 | June 17th, 2004 06:10 PM |
Summing marked cells | Sam | Worksheet Functions | 2 | October 2nd, 2003 11:59 PM |
COUNT visible cells? | Gregg Stone | Worksheet Functions | 3 | September 22nd, 2003 03:07 PM |