How do I subtotal data in Numbers?

Data: All entries copied from your sample and pasted into this table.

Summary-person:

Summary-Category:

These two tables use the SUMIF formula below, entered into B2 on the table and filled down to the last row in column B.

S-P: SUMIF(Data::A,A2,Data::C)

S-C: SUMIF(Data::B,A2,Data::C)

Syntax: SUMIF(test-values,condition,sum-values)

Summary-person and category:

This table uses the SUMIF formula below, entered into C2 on the table and filled down to the last row in column C.

S-P&C: SUMIFS(Data::C,Data::A,A2,Data::B,B2)

Syntax: SUMIFS(sum-values,test-values,condition,test-values,condition)

Furthers pairs of test-values,condition can be added. ALL conditions must be met for a value to be included in the sum.

Because of the reversal in the order of sum-values and test-values from SUMIF to SUMIFS, some users will use SUMIFS exclusively to maintain consistency even where only one condition is needed.

Here's a more compact arrangement of the Person and Category summary table. The formula in B2 of this table is shown below the two tables. The Absolute reference operators ( $ ) keep the column or row reference the same as the formula is filled down and right from B2 to E4.

Regards,

Barry

iwaddo wrote:

OK, so in Numbers I'd be interested to how you would I summarise this data. In Excel I'd Pivot it.

I want to know

1. How much each person spent?
2. How much was spent on each category?
3. How much each person spent on each category?

Name	Category	Amount
Barry	Red	1
Fred	Blue	2
Barry	Red	3
Ian	Blue	2
Ian	Red	4
Barry	Green	1
Fred	Yellow	5

Thank you for your help

Something like this?

I produced that "crosstab" summary with a quick menu choice and answering prompts. Like a Pivot Table in Excel, it was quick, a few seconds.

It's in an Crosstablulate Selected Table Automator Service (Dropbox download) that contains an AppleScript.

To install just double-click the downloaded .workflow package and give permission at System Preferences > Security & Privacy. Also make sure Automator.app is checked at System Preferences > Security & Privacy > Privacy > Accessibility.

To use just click in the table containing the data, and choose 'Crosstabulate Selected Table' from the Numbers > Services menu. Answer the three prompts asking what you want to do, and the results are generated automatically and placed in a separate table, similar to a Pivot Table.

Unlike a Pivot Table this refreshes automatically if you change data in the source table. But like a Pivot Table if you add new categories to the source data then you have to manually refresh by running it again.

Trying this should take a minute or so. If it doesn't do what you want, in Finder hold down the option key and in the menu Go > Library > Services and delete it the way you would any other item in Finder. But it's saved me a lot of time, and I think you will find it useful.

SG

Hi iwaddo,

Perhaps this thread will help, where SG shows an AppleScript and a formula method to find unique entries in a list:

Filter Table menu doesn't use a scrolling dropdown

SG was a hero in the transition from Numbers 2 (which had a "Categories" feature similar to pivot in Excel) to Numbers 3 and 4 where we must now use SUMIF, SUMIFS, COUNTIF or COUNTIFS to "pull" subtotals from a large list of data into several "summary" tables. That is how Numbers works best 😉.

Regards,

Ian.

A "subtotal" is simply a total of a subset of he items. SUMIF includes items in the sum IF they meet the specified condition—which often means IF the items have been assigned to a specific category.

How would you tell the subtotal function to decide which items to include in the subtotal?

A quick look at Excel's SUBTOTAL function shows it to be similar to, but wider ranging than, the …IF(S) functions in Numbers—SUMIF(S), COUNTIF(S), AVERAGEIF(S), without the need to hide the not-to-be-included values in the list.

Regards,

Barry

The person list can be updated automatically by formula, as can the colour list.

Both can also be updated using an AppleScript or by an Automator Service (which runs an AppleScript).

Manual updating of the single condition tables is a matter of adding a row and entering the new name or colour in the new row of column A. The formula will be copied into the new row automatically.

For the two condition table (second example) Add a row and enter the name for a new person; add a column and enter the colour for a new colour. As above, the formulas should copy into the new row or column automatically.

Regards,

Barry

Hi iwaddo,

Take a look at SUMIF in the function browser.

Click on any cell in a table, then type = to open the browser in the right sidebar. Type SUMIF in the search box, then click on SUMIF in the results list.

You'll get a description of the function, its syntax, and several examples showing how to use it.

Call back with any questions.

Include a sample of your data table howing the columns you want summed and the columns containing the catgories for which you want subtotals.

Regards,

Barry