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, …)

(includes hidden values)
(ignores hidden values)
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VAR

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s