How can I make the autosum refer to visable rows / filter?

[Maria]

I have made a excel sheet with filter function. When I use the filter, I want
the sum to reflect the rows that are visable, ie I want the sum to change
when I use the filter. It worked before, but now suddenly it doesn't anymore.

 

[Mike H]

Maria,

Are you sure it wasn't subtotal that worked before

=SUBTOTAL(109,B2:B20)

With data in B2 to B20 this will sum visible cells only

Mike

 

[Maria]

Hi Mike,

Thanks for trying to help
I have used different formulas in the bottom row, and they always changed
when I made a selection in the auto filter. One example is average, which
doesn't change when I do a selection in the autofilter. This means that I
have to manually do a calculation for each selection.

Best regards,
Maria

 

[Gord Dibben]

Look up SUBTOTAL function in Help and you will see 11 diferent numbers you can
use for a function.

=SUBTOTAL(1 or 101,range) for Average


Gord Dibben MS Excel MVP

 

[T. Valko]

If you have the autofilter applied and the data is filtered on some
criteria, if you then use autosum (or auto-whatever) on the filtered data
range Excel (2002) automatically uses the SUBTOTAL function.

 

[Gord Dibben]

Never new that...................or forgot maybe more likely<g>

Thanks for bringing it to the table.


Gord

 

[Gord Dibben]

And my spelling is getting as bad as my recall<more g>

Gord