Auto Filter + subtotal
 

How I can add a subtotal/total line below a list with auto-filter ?

=SUBTOTAL(9, B2:B200)
but the subtotal must be seen either by including a value that the
filter will allow to be displayed, or by being outside the filtered rows.

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"alex" wrote in message ...
How I can add a subtotal/total line below a list with auto-filter ?

And remember that =subtotal() will ignore those values hidden by the autofilter.

You may want to dump the autofilter and (if your data is sorted nicely) use
Data|Subtotals.

alex wrote:

How I can add a subtotal/total line below a list with auto-filter ?

--

Dave Peterson

thanks a lot. i was putting the SUBTOTAL inside the filtered range, so it was
disappearing...



"David McRitchie" wrote:

=SUBTOTAL(9, B2:B200)
but the subtotal must be seen either by including a value that the
filter will allow to be displayed, or by being outside the filtered rows.

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"alex" wrote in message ...
How I can add a subtotal/total line below a list with auto-filter ?

I am using Advanced Filter and subtotal. I am using the function
Subtotal(9,A1:A22)

Once I change the filter criteria, I had thought, subtotal will
automatically calculate, but in my case it is not. However, if I double click
on the cell where I have subtotal and hit Enter, it updates the calculation.

Is there something that I am missing and if you know a fix for this I would
really appreciate any help.
Thanks,
--
Anand Chhajed


"alex" wrote:

How I can add a subtotal/total line below a list with auto-filter ?

Do you have calculation set for automatic?

Tools|Options|calculation tab.

Anand wrote:

I am using Advanced Filter and subtotal. I am using the function
Subtotal(9,A1:A22)

Once I change the filter criteria, I had thought, subtotal will
automatically calculate, but in my case it is not. However, if I double click
on the cell where I have subtotal and hit Enter, it updates the calculation.

Is there something that I am missing and if you know a fix for this I would
really appreciate any help.
Thanks,
--
Anand Chhajed

"alex" wrote:

How I can add a subtotal/total line below a list with auto-filter ?

--

Dave Peterson

Yes, that is set for automatic.

Also, there is a tab for Calc Now(F9). Even if you click on that it does not
calculate.
But, as I said if I double click on that cell, it calculate automatically.
Appreciate your help!

Thanks,
Anand
--
Anand Chhajed


"Dave Peterson" wrote:

Do you have calculation set for automatic?

Tools|Options|calculation tab.

Anand wrote:

I am using Advanced Filter and subtotal. I am using the function
Subtotal(9,A1:A22)

Once I change the filter criteria, I had thought, subtotal will
automatically calculate, but in my case it is not. However, if I double click
on the cell where I have subtotal and hit Enter, it updates the calculation.

Is there something that I am missing and if you know a fix for this I would
really appreciate any help.
Thanks,
--
Anand Chhajed

"alex" wrote:

How I can add a subtotal/total line below a list with auto-filter ?

--

Dave Peterson

Do you see Calculate in the statusbar (bottom left corner) when you filter on
something else?

xl2002 (I think) added an option to turn calculation off on a sheet by sheet
basis.

But this is extremely rare (in my experience).

What version of excel are you using?

(I don't have a real good guess.)

One thing to try if you're at your wit's end:

Select all the cells
edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Maybe it'll make the next change calculate correctly.

Anand wrote:

Yes, that is set for automatic.

Also, there is a tab for Calc Now(F9). Even if you click on that it does not
calculate.
But, as I said if I double click on that cell, it calculate automatically.
Appreciate your help!

Thanks,
Anand
--
Anand Chhajed

"Dave Peterson" wrote:

Do you have calculation set for automatic?

Tools|Options|calculation tab.

Anand wrote:

I am using Advanced Filter and subtotal. I am using the function
Subtotal(9,A1:A22)

Once I change the filter criteria, I had thought, subtotal will
automatically calculate, but in my case it is not. However, if I double click
on the cell where I have subtotal and hit Enter, it updates the calculation.

Is there something that I am missing and if you know a fix for this I would
really appreciate any help.
Thanks,
--
Anand Chhajed

"alex" wrote:

How I can add a subtotal/total line below a list with auto-filter ?

--

Dave Peterson


--

Dave Peterson