Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

# Question:Q:Subtotals in Numbers

I'm doing my taxes and have a spreadsheet that includes columns for Transaction Type, Category, and Labels, and other data.

I would like Numbers to Subtotal the Amount column whenever one or more of the above three columns changes.

Similar to a multi-column sort, and the sub-total, in Excel.

Thanks in advanced for any suggestions. 🙂

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

Posted on

Hi Wanderscribe,

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

Mar 13, 2018 4:02 PM

Mar 22, 2018 1:54 PM in response to wanderscribe In response to wanderscribe

Barry,

Sorry for the late follow-up.

Thanks for the detailed reply and pointing me in the right direction.

I will take on board your suggestion regarding version numbers when posting.

Thanks again!

Alex

Mar 22, 2018 1:54 PM

User profile for user: wanderscribe

Question: Subtotals in Numbers