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.

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" wrote:

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.

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

"Mike H" wrote:

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" wrote:

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.

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

On Mon, 11 Feb 2008 06:17:03 -0800, Maria
wrote:

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

"Mike H" wrote:

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" wrote:

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.

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.

--
Biff
Microsoft Excel MVP


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Mon, 11 Feb 2008 06:17:03 -0800, Maria

wrote:

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

"Mike H" wrote:

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" wrote:

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.

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

Thanks for bringing it to the table.


Gord

On Mon, 11 Feb 2008 17:37:18 -0500, "T. Valko" wrote:

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.