Subtotal a filtered range

[mdarcey]

hi

does anyone know how to subtotal a range that has been filtered? I am aware of the 109 function, but this only ignores cells that are hidden

so lets say column A details - Jan, Feb, March, April
column B has monthly totals

I then filter A to show only Jan & Feb. If I subtotal column B the sum includes the value's for March and April?

Any ideas?

[Paul]

Hi MDarcey,

If you use the normal SUBTOTAL functions (1-11) it will account for filtered lists and only subtotal the visible cells (at least that's how it works in 2007).

[daddylonglegs]

I am aware of the 109 function, but this only ignores cells that are hidden

No, that's not correct. This formula only totals those values not hidden by filtering

=SUBTOTAL(9,A:A)

If you change the 9 to 109 then rows hidden by filtering will still be excluded...additionally any rows hidden using the Hide command will also be ignored. If you never use Hide then either one should work

[Cutter]

Here's some info on the SUBTOTAL() function.

http://www.excelfunctions.net/Excel-...-Function.html

[mdarcey]

thanks guys,

had a play about in work this morning and seen the different results.

Cheers