Subtotal filtered columns

Hi Kevin,

Just in case our replies are "crossing in the mail" revisit your thread.

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

Dropbox - Kevin.numbers

Regards,

Ian.

Hi Kevin,

A filter hides rows from view, but they still feature in the sum. Table 1-1 is filtered to show F

SUMIF will work. This table has a Header Row and three Footer Rows

Formula in D10 (and Fill Down to D11)

=SUMIF(C,C10,D)

Regards,

Ian.

Hi Kevin,

Or, we could do without Pop-Up Menus and create summary tables for each category. Cells in column A of the summary tables are formatted as Automatic.

Adding Fred and Susan to the Database (one more female, one more male, both with black hair)

Regards,

Ian.

Edit: I received your latest email notification from Apple Support Communities.

Thanks again. You were very helpful. Numbers just seems to pale in comparison to Excel. But I am hoping that it is learning curve on my part.

We are all learning!

Thanks for this challenge.

Happy Numbering,

Ian.

I must have misunderstood. I thought you were filtering the entire table and you want the sum of the rows that remain visible, ignoring the rows that are filtered out or hidden. If you do your filtering then select the column after doing the filtering, the SUM at the bottom of the window should be the result of only those rows that are visible.

No. The $ symbol keeps it locked to a row, not a column. It should be possible to fill right.

And, because the formula refers to a Footer Row (C9), adding more rows automatically updates the formula to C11.

Sum of D (now with Fred, age 10 and Susan age 10) is updated to 239

Please test and call back.

Regards,

Ian.

I think I know what you mean about memory.🙂

This is a neat little trick that Badunit has pointed out. Filter, select the visible cells, drag the sum below into a cell. A formula summing the visible cells is automatically generated, so when you "unfilter" the table the formula still works. In some situations quicker and easier than setting up a SUMIF or SUMIFS.

SG

The formula is hardwired to the original three rows. When you add rows, or there is a sex change operation, you'll need to re-drag. It's a really handy thing for getting a quick subtotal, particularly for a complex filter that would be time-consuming to replicate with SUMIF and SUMIFS, etc.

But if the data undergoes frequent updates and you need an automatically updating summary, you'll need SUMIF and SUMIFS.

Suggest you try it out when you have access to Numbers.

SG

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

Hi Kevin,

You can set the tables up to add new categories to the Summary table(s) as they are added to the Data table. Here's a sample, using your initial data, and adding the two rows in Ian's extended set.

Data has an added column, G, to hold an index marking the first row where each hair colour appears. The index is created by the formula shown below the table, entered in G2, then filled down to the bottom cell of the table. The column may be hidden (and should be, to help prevent accidentally 'editing' the formula).

The Summary table contains two formulas:

A2: IF(ROW()−1>MAX(Data::G),"",INDEX(Data::F,MATCH(ROW()−1,Data::G,0)))

The core formula, in bold, uses match to find the index value for this row ( ROW()-1 ) in column G of Data, and passes that back to INDEX, which returns the value in the indexed row of column F.

The part of the formula in normal text checks the index value for this row against the maximum value in the index column, and prevents the calculation when all colours have been listed.

B2: SUMIF(Data::F,A2,Data::D)

Sums all rows of column D of Data where that row of column F shows the same label as in A2 of 'this table'.

Both formulas are filled down to the last row of the table.

When rows are added to Data, the formula in column G is automatically filled into the new rows, and new index values are calculated as needed.

When rows are added to the Summary table, the formulas in both columns are automatically filled into the new row(s), and their results are displayed:

Here, I've added only the age and hair colour data to Data, as these are the only entered values that enter into the calculation on this summary table.

I added two rows to Summary to show the results when there are more rows than needed.

With the extra row (7), a zero result is displayed in column B until there is a new colour recorded in Data::F, at which point the new colour will be immediately added in the empty row, as will the sum of age(s) for that colour.

Regarding 'dynamic' pop-up menus:

Not a supported feature, but you can easily create new or add to existing menus.

All selected cells in the table originally contained text. By selecting the cells, then changing the Data Format from Automatic to Pop-Up menu, Numbers changes all of these cells to Pop-Up menu cells, each containing the four items shown, in the order they individual items first appeared in the list, and each set to the item matching the text that was in that cell.

To add "Black" to the menu, I

added rows to the table to accommodate the two new entries,

selected the two new 'Hair' cells and changed their format to Text,

Entered "Black" in both cells,

Selected the two cells AND at least one of the pop-up menu cells above it,

Then changed the Data Format pop-up from Multiple to Pop-Up menu, with the results shown below:

All selected cells now contain pop-up menus with the five items shown.

Unselected cells in the same column still contain the previous version of the pop-up, with only the first four items.

Edited cells maintain their original pop-up settings.

Regards,

Barry

It is the total of all the visible rows. I notice if you select the column first, then apply your filter(s), you can see the results change.