Sum a filtered range
 

Can anybody help me with the calc to sum a filtered range?

Sum a filtered range
 

Hi Peter

=SUBTOTAL(9,A3:A1000)
I usually like to put such values at the top of my column, so assuming there
was a header in row 1 and data from row 2 downward, I would Insert a new row
at row 1 and put the above formula in cell A1
Select cell A3WindowsFreeze Panes and your header and Subtotal will always
be visible.

The above formula works with all versions of Excel and totals the values of
cells that are still visible after an Autofilter has been applied
From XL2003 onward, the facility to also ignore Manually hidden rows was
also introduced, you just use 109 in place of 9
=SUBTOTAL(109,A3:A1000)

Take a look at Help on Subtotal, and you will see what the other parameters
1 through 8 also provide.
--
Regards
Roger Govier

"Peter" wrote in message
...
Can anybody help me with the calc to sum a filtered range?

Sum a filtered range
 

Hi Roger,

Thanks for your help... works great - I'm a happy Excel player again!

Regards, Peter

"Roger Govier" wrote:

Hi Peter

=SUBTOTAL(9,A3:A1000)
I usually like to put such values at the top of my column, so assuming there
was a header in row 1 and data from row 2 downward, I would Insert a new row
at row 1 and put the above formula in cell A1
Select cell A3WindowsFreeze Panes and your header and Subtotal will always
be visible.

The above formula works with all versions of Excel and totals the values of
cells that are still visible after an Autofilter has been applied
From XL2003 onward, the facility to also ignore Manually hidden rows was
also introduced, you just use 109 in place of 9
=SUBTOTAL(109,A3:A1000)

Take a look at Help on Subtotal, and you will see what the other parameters
1 through 8 also provide.
--
Regards
Roger Govier

"Peter" wrote in message
...
Can anybody help me with the calc to sum a filtered range?