Subtotal filtered columns

Question

Subtotal filtered columns

I have read several posts on this issue and none do what I want.

Say I have 4 columns and 7 rows. I want to filter Column 3 on a criteria. When I filter I now have 3 rows instead of 7 displayed ( Not necessarily starting at Row 2 but maybe Row 4 if that is where the first data is from that filter). I want to total only the values of Column 4 that match the filter in Column 2 (which may change next time I filter).

Unflitered Table Example

Row	Name	Sex	Age
1	John	M	12
2	Sid	M	25
3	Andy	M	17
4	Betty	F	63
5	Susan	F	48
6	Ashley	F	39
7	Mike	M	15

I want the total ages of the women so the filtered table looks like this

Row	Name	Sex	Age
4	Betty	F	63
5	Susan	F	48
6	Ashley	F	39

So the answer should be 150. And if I filter on Males the table would look like this

Row	Name	Sex	Age
1	John	M	12
2	Sid	M	25
3	Andy	M	17
7	Mike	M	15

And the answer should be 69

Airport Extreme 802.11ac

Posted on Apr 8, 2018 8:24 PM

Reply

Answer 1

Badunit

Level 7

30,582 points

Apr 9, 2018 4:49 PM in response to kevinfromherndon

An easy way to see the sum you want is to select the column (click on the column letter) then look at the bottom of the Numbers window where you will see Sum, Average, etc. of only the visible cells of that column. If you need the sum on the table, such as for printing the table, you can drag the sum up to the table and place it in a cell. I realize this is not a totally automated way of doing things but it is very flexible and a possible solution given we have no subtotal function.

Reply

Answer 2

Yellowbox

Level 7

21,939 points

Apr 10, 2018 6:17 AM in response to kevinfromherndon

Hi Kevin,

Add a new column E as the trigger for a filter (Column "Show?" Text is Show):

Pop-Up Menu in Footer Row C9

Formula in E2 (and Fill Down)

=IF(C$9="Show All","Show",IF(C2=C$9,"Show",""))

Now choose M from the Pop-Up Menu in C9

SUM in D9 sums only the visible rows. This is what is going on "behind the curtain"

=SUMIF(E,"Show",D)

Tidy up by hiding Column E (and I changed C9 to F)

Give this a try and we can help you extend the idea to other columns.

Regards,

Ian.

Reply

Answer 3

Yellowbox

Level 7

21,939 points

Apr 9, 2018 7:29 AM in response to kevinfromherndon

Hi Kevin,

Drop down lists (in Numbers, called Pop-Up Menus) will work.

Numbers is designed to work by "pulling" relevant data from a Database table into several small tables, each with a purpose. Think of the small tables as "Presentations" for display or printing. Then there is no need to filter or select to print part of the Database. Simply move (cut and paste) your summary table(s) to another sheet. Formulas will automatically adjust. Print only that sheet.

In each small summary table, A2 is a Pop-Up Menu:

Formula in B2 =SUMIF(Database::F,A2,Database::D)

Regards,

Ian.

Reply

Answer 4

SGIII

Level 8

37,550 points

Apr 10, 2018 8:20 AM in response to kevinfromherndon

kevinfromherndon wrote:

I believe that gives me totals of the entire column which is not what I need for this but might be helpful in the future.

Why you "believe" that! Try it. You'll see that it gives the total of the visible cells only after applying a filter.

Thanks for pointing this out, Badunit!

SG

Reply

Answer 5

Apr 9, 2018 6:02 AM in response to Yellowbox

Thanks. That is helpful but not ideal. In reality my table has about 10 columns and each column has about 3-4 choices I might want to sort by and total. So that makes a lot of footer rows, formulas and calculations. Is it possible to make cell C10 a variable based on the value of the top cell in the filtered table which in this case would be row 4 ? It is too bad Classifications were eliminated. That would have been the perfect solution for me. So easy in excel to just use SUBTOTAL.

Reply

Answer 6

Apr 9, 2018 6:07 AM in response to kevinfromherndon

Maybe I could make 9B a drop down list of column B and 9C a drop down list of column C. Possible ? Work ? I select the list feature I want to subtotal for in the footer and the subtotal works because that cell is constant because it is in the footer not the body?!?!?!?!

Reply

Answer 7

Apr 9, 2018 9:04 AM in response to Yellowbox

To be clear. When you formatted Column A of the Summary Tables as Automatic did it pick up the Black hair on its own or did you have to add it yourself ? That looks like the easiest solution for what I need. The subtotals would always be displayed and no need to filter most of the time.

Reply

Answer 8

Yellowbox

Level 7

21,939 points

Apr 9, 2018 12:16 PM in response to kevinfromherndon

Hi Kevin,

After I added Fred and Susan and the new Hair Colour (Black) to the Database:

I had to add Black to this summary table and copy this formula into column B

=SUMIF(Database::F,A2,Database::D)

I am not using filters.

Have a look at the document here (click on Download to view):

Dropbox - Kevin.numbers

Regards,

Ian.

Reply

Answer 9

Apr 10, 2018 6:10 AM in response to Badunit

I will try it when I get home. It sounds like it will work. Thanks. I guess I was not paying proper attention to it. I thought it was the total of the whole column. I hope that works cause it would be easy !!!

Reply

Answer 10

Apr 10, 2018 8:24 AM in response to SGIII

I would "believe" that because I am at the office and can not try it and am operating from memory only - which at my age is unreliable - LOL. I planned on trying it all at home as soon as I can get back there.

Reply

Answer 11

Apr 10, 2018 8:45 AM in response to SGIII

So, if I hear you right, 1. filter the column, 2. highlight the column I want totaled, 3. drag the sum to the cell I want it in. When I un-filter the column the total will reflect all the results. When I re-filter it (perhaps on a different criteria) the new subtotal will appear. Correct ?

Reply

Answer 12

SGIII

Level 8

37,550 points

Apr 10, 2018 8:59 AM in response to kevinfromherndon

Almost. Let's say you already have a SUM that sums the whole column. Then filter the table, select the visible cells, and drag the 'SUM' token into another cell. Now when you "unfilter" the table the original SUM will still sum the whole column but the other cell into which you dragged the token from below will still show the subtotal.

It's a little scary that Badunit found his own advice via a search. He may be one of those people who have "forgotten" more than the rest of us ever knew to begin with.🙂

SG

Reply