Excel tip: Sum of visible filtered rows

I am a big fan of the MS Excel AutoFilter feature, and use it extensively. Every now and then I’m wished that there was some function within Excel to do a SUM function, but only on the rows that are visible as a result of the AutoFilter… well few minutes on Google and a found it.

Use the subtotal function with the relevant function_num, for addition I used 109. For example: =SUBTOTAL(109, A1:A1000)

Syntax: SUBTOTAL(function_num, ref1, ref2, …)

Function_num
(includes hidden values)
Function_num
(ignores hidden values)
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 VAR
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s