Excel AutoFilter and Subtotals together? 1

[Stroppy]

Hi all. I am learning excel. As an example, I have a simple little spreadsheet:

Month Items sold
Jan 300
Jan 150
Jan 75
Feb 87
Feb 98
March , etc, etc etc.

I have learnt how to use AutoFilter and how to do subtotals. Now, do they both work together? For example, having applied subtotals to the sheet, I then apply autofilter. (Why? Not sure....) Thing is, in AutoFilter, Jan Feb and March Total is shown as zero. Is there a reason for this? Should the 2 not be used at once?

Thanks, as usual, in advance.

 

[karenbobby]

Hello,

Would recommend you to use Pivot Table as I think it could do what you want.

Good Luck!

 

[xlbo]

That would depend on whether you are using the subtotal WORKSHEET FUNCTION or Data>Subtotals via the menu path
The subtotal function will work very happily with autofilter. I rather think that Data>Subtotals will not because you don't have a month NAME record on the subtotal line and therefore it gets filtered out Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Stroppy]

Geoff, I checked out th subtotal worksheet function, and from what I can see, the formula I would enter into the monthly subtotal would have the same syntax as the formula that Data-Subtotals-...etc puts in. It's not really a big thing, just that I was teaching people to do subtotals and autofiltering, and they noticed (!) that if they applied subtotals, then autofiltering, that when they selected Feb Total from the Autofilter list, that the subtotal beside it shows 0. Even though in the sheet itself it's ok.

I just had yet another look...If I choose January in Auto filter, it displays each of the entries with a Grandtotal for January only. OK. At least I can tell them it is possible to filter totals on a monthly basis.

It actually appears to be inconsistent. Depending on how it is done, the info is shown or not shown. collapsing headings on the outlining toolbar in a certain order works, as does reclicking "show all" on the auto filter option ... I think I'll move back to Sheet Protection. Something else to addle my brain!

Thanks to xlbo and karenbobby

 

[xlbo]

Well, I know what's happening.....
SUBTOTAL works on VISIBLE cells only. If you choose "Feb Total", all the actual Feb cells will be hidden (as they do not match "Feb TOTAL&quot therefore, the subtotal will be zero as the Feb cells are hidden
If, however, you select "Feb" in the month column, the Feb cells are visible and therefore the subtotal will work Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Stroppy]

That explains it! Thanks

M