Auto Filter + subtotal

[alex]

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

[David McRitchie]

=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" <[email protected]> wrote in message news:[email protected]...
> How I can add a subtotal/total line below a list with auto-filter ?

[Dave Peterson]

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

[alex]

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" <[email protected]> wrote in message news:[email protected]...
> > How I can add a subtotal/total line below a list with auto-filter ?
>
>
>

[Anand]

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 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

[Anand]

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]

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