Sum Visible Rows
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.
Peter:
Interestingly, the subtotal function only works this way with “filtered” lists, and continues to sum rows which are just “hidden”.
4 May 2004, 4:50 pmJuan Pablo:
Peter,
That is one of the new features in Excel 2003. They introduced new parameters to fix that issue.
6 May 2004, 11:03 amAna:
Hi,
And how do we do this in a Macro?
How do I navigate between the visible rows only?
Thanks in advance,
29 December 2004, 6:47 amA.
excelerater:
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?
2 September 2005, 12:23 pmPatrick Sedgwick:
I had this problem with hidden (as distinct from hidden-after-filtering) rows. Microsoft have a solution:
24 November 2005, 8:27 amhttp://support.microsoft.com/?kbid=150363
Mohammed:
Thank you! Saved few hours of my time today.
15 January 2008, 11:51 pm