When you’ve filtered data in Excel, the SUM function still sums cells even if they’re not visible. To SUM only the visible data, you can use the SUBTOTAL function. SUBTOTAL ignores hidden rows and columns.
In this example, there are 2156 rows of data that are filtered so that only those rows whose City is ‘Paris’ are shown.
The formulas below the filtered data are:
C2159 - =SUM(C2:C2157)
D2159 - =SUM(D2:D2157)
C2160 - =SUBTOTAL(9,C2:C2157)
D2160 - =SUBTOTAL(9,D2:D2157)
You can do more than just sum with SUBTOTAL. The first argument (9) is what tells SUBTOTAL to sum. Look up SUBTOTAL_worksheet_function in help to see the other possible arguments.
Interestingly, the subtotal function only works this way with “filtered” lists, and continues to sum rows which are just “hidden”.
Peter,
That is one of the new features in Excel 2003. They introduced new parameters to fix that issue.
Hi,
And how do we do this in a Macro?
How do I navigate between the visible rows only?
Thanks in advance,
A.
in excel 2202, i entered the following
a1 = subtotal(9,a:a)
a2 = data autofilter
a3 = data
…
…
a99 = data
it worked when i first created it, to automatically
add up the visible data that was filtered by the
autofilter.
now it stopped working and complains about a circular reference. the “subtotal” function is suppose to be smart enough to ignore other subtotals so values are not double counted.
i want the total at the top, so that it is always visible and so that i can add more data at the bottom
anyone have any ideas?
I had this problem with hidden (as distinct from hidden-after-filtering) rows. Microsoft have a solution:
http://support.microsoft.com/?kbid=150363
Thank you! Saved few hours of my time today.
You’ve saved my fingers a lot of pain :)
This will work. To sum it up with subtotal and not include hidden rows use a formula like this =SUBTOTAL(109,A2:A5) where 109 tells the subtotal function to sum and ignore hidden rows.
MS reference for the subtotal function http://office.microsoft.com/en-us/excel/HP100624631033.aspx.
nice tip. saved my time and effort to write vba codes.
What is 9 (9,range)
Great tip. helped me a lot and saved lots of time
Excellent post.
For the other function values,
Column 1: Function_num (includes hidden values)
Column 2 : Function_num (ignores hidden values)
Column 3 : Function
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP
So, a “9? is a SUM() of all visible and hidden cells in the range and a “109? is a SUM() of only visible cells in the range.
It took me a while to realise that hidden is not the same as filtered. Filtered rows aren’t hidden according to MS. Semantics is obviously not a strong point in the MS camp.
But, I very very rarely manually (or programmatically) hide rows, but I filter all the time.
Thanks Dick,
But strange ….
I try on horizontal way…. =SUBTOTAL(9,H2:AI2)
The formula is still count Hidden cell.
I have no idea why?
Please help….
Thankss
problem sovled …
Thanksss