Subtotals in Numbers

Hi Wanderscribe,

Your question is ambiguous.

Do you want a subtotal of all amounts in the Amounts column from the beginning to the row in which one or more of the three labels changes?

Or do want three sets of subtotals: one for all transactions of a specific Type, a second for all transactions in a specific Category, and a third for all transactions with a specific Label?

Numbers recalculates a table whenever there is a change that could affect at least one formula on the table, so the 'whenever' part will likely be 'whenever any change is made in the data' (including any changes in the labeling).

Here are examples of both types of subtotal. Column F of the larger table shows the Amount Total to any row in which the type, category or label is different from the type, category or label in the row above.

F2:

General practice with Numbers would be to calculate this information on a separate table, as shown on the smaller table below the one on which the data is recorded. The formula in B2 of this table is shown below the table. Cell highlighting on the two tables matches the colour of the cell or range references in the formula.

The formula is filled down from B2 to the last row of this table containing a 'Type' label.

The same formula, with cell and column references edited as necessary, is used in D2 and F2, and filled down in the same manner.

B2: SUMIF(Table 1::B,A2,Table 1::E)

D2: SUMIF(Table 1::C,C2,Table 1::E)

F2: SUMIF(Table 1::D,E2,Table 1::E)

This group of cells could be constructed on the same table as the data collection is done, as shown, but might not play well if the table is sorted. Numbers sorts tables on the contents of one or more tables, but does not limit the sort to selected columns. The sort regards each row as a 'record', and keeps that record together by moving the entire row.

"I'm running a 2017 MacBook Pro, High Sierra, latest version of Numbers."

This may be true on the day the question was posted, but won't be when it is read later by someone looking for the answer to a similar question. Please use version numbers when specifying the OS and software versions, as these can often affect the answers.

Regards,

Barry