Possible to have a (single) parent category that sums subcategory sums?

Question

Level 1

4 points

Possible to have a (single) parent category that sums subcategory sums?

I have a sheet with categories (each Summary row is a type of dietary supplement (e.g., "B complex")), and a column with a (currency-formatted) cel that displays a "Summary" of "Sum", which is the sum of the numbers ("TOTAL cost / day") in the (numbered/actual) rows (in one of my screenshots, that cel has a green background). This works fine:

B complex [category] Total [currency format] Sum (summary) of "TOTAL cost / day" e.g., $0.33

Thiamin [row] Cost [currency format] [cel] e.g., $0.33

Riboflavin [row] Cost (currency format) [cel] e.g., $0.

(Note: I sometimes have more than row per category with different ingredients in a given supplement, but only list "Cost" in the first row to track the daily cost of the supplement. The reason for multiple rows is to track daily amounts taken of each supplement's ingredients, but I'm not trying to somehow track the cost of each ingredient.)

I want to then have a single cel (in the sheet) that sums all those sums.

OPTION 1 (Use subcategories)

From Numbers help: "A table can have one main category and up to four subcategories to create a hierarchy for your data. You can change this hierarchy, rename or delete categories, and more."

I tried selecting "Add Category" from the toolbar and selecting the column: "TOTAL cost / day", and that makes the new category, and also sub groups rows which have $0.00 together, and then the one row with the actual cost, which I don't want. However even if that weren't a problem, it still doesn't solve having a single parent category for "TOTAL cost / day" listing the sum of all of the subcategory "TOTAL cost / day" sums.

B complex [parent category]

$0.33 [subcategory]

B complex - Thiamin [row] TOTAL cost / day $0.33

What I want to do (if possible) is to have a sum of all "TOTAL cost / day" amounts.

TOTAL cost / day [parent category] (e.g., $0.54)

B complex [subcategory]

B complex - Thiamin [row] - TOTAL cost / day [cel] (e.g., $0.33)

Vitamin D3 & K2

Vitamin D3 & K2 - D3 - TOTAL cost / day (e.g., $0.21)

Also, if I can do that, how do I nest the subcategories under the (one) parent category 'row'?

"If your table contains subcategories, you can move a category up or down one or more levels to reorganize your data. For example, if you have a table that categorizes shirt sales by sale date and then by order status, you could reorganize to categorize by order status first and then by sale date.

In the Organize sidebar, click Categories.

Drag the Drag button up or down next to the category you want to move."

However when I do that, I see no way of creating just one "TOTAL cost / day" parent category 'row'.

Do I need to start from scratch with a new sheet, creating the (one) parent category, create a placeholder subcategory, and then somehow copy and paste the current categories from the initial sheet into that subcategory 'space'?

OPTION 2 (Use a separate sheet for the sum calculation.)

This doesn't appeal to me because I'd like to see the 'Sum of sums' on the same sheet, but this method does work. The cel on the summary sheet's value is: SUM('TOTAL cost / day'), i.e., the column. I'm going to use this method for now, and then see if I can get subcategories to work.

MacBook Pro 14″

Posted on Apr 29, 2025 1:03 PM

Reply

Answer 1

Top-ranking reply

Camelot

Level 9

58,299 points

Apr 29, 2025 3:01 PM in response to bob7

I think I understand your workflow, and threw together something that I think looks similar.

Ultimately, when using categories, you need to think 'backwards' of what data is it you're trying to summarize/categorize. In this case, you have the main 'family' group ('B Complex', 'Vitamin C', etc.), and you have categorization working for those. However, to sum them, you need another level of categorization.

I did this by inserting a new column BEFORE the Short Name column (I called it 'Dummy', and the actual cell entry is irrelevant, but needs to be the same for all cells. You can also hide this column later if you don't want to see it.

Now you can add another level of categorization based on this dummy field. Since it contains the same values in all cells, it creates one more level of subtotals:

Here's my Categorization settings:

And here's the top of the table showing the subtotals:

(I just threw some bogus data into 'Vitamin C' to get some value.

Reply

Answer 2

bob7 Author

Level 1

4 points

Apr 29, 2025 3:43 PM in response to Camelot

That is excellent work! It worked perfectly. Thank you so much.

I was concerned that I'd have to remember to enter: "Sum of sums" in any new groups/rows, but when I ran both: "Add Group Below" and "Add Row Below", the text was automatically populated. Bonus.

When I created the new category, it was below the "Short name…" category, so I had to move: "Sum of sums" above it. In case any else wants to try your method, I've included the steps I took, but they're almost 100% the same as what you described:

Select column A (short name …), then select: "Add column header", and name it: "Sum of sums."

Enter "Sum of sums" in that column, first row, then copy that text and paste it into all the cells in that column.

In Categories, select: "Add a category…", and select: "Sum of sums".

Move: "Sum of sums" above the: "Short name…" category.

At the intersection of the: "TOTAL cost / day" column and the: "Sum of sums" Summary row, select: "Sum".

Reply

Answer 3

bob7 Author

Level 1

4 points

Apr 30, 2025 9:01 AM in response to bob7

I discovered one design feature I would like, but haven't found a fully elegant solution.

I would like to have the parent category ("Sum of sums") row to freeze, so it is visible if I scroll down more than one (screen) page of rows. Numbers allows header rows to freeze, but Label Row and Summary Rows are not header rows. I changed Header Rows from 1 to 3, and was then able to copy the cells in the Label Row and Summary Row in the: "TOTAL cost / day" column, then paste into rows 2 and 3 (Header Rows), and the data shows up correctly (and updates if I make a change in the table data). This keeps the data visible even if scrolling rows. I also manually entered: "Sum of sums" in those same header rows to be consistent with what is displayed in the Label Row and Summary row of the: "Sum of sums" Label and Summary Rows.

An annoyance is that the "Sum of sums" Label and Summary Rows remain visible, and are redundant with the additions I made to Header Rows 2 and 3 (unless I am scrolled down beyond one display page of data).

Label and Summary Rows cannot be hidden, so I tried to set their row height to 0, but that gets changed to 0.13" after I enter it, and that's sort of worse than just living with the redundant data (because the rows at 0.13" have data that is partially 'cut off' from view).

Reply

Answer 4

Camelot

Level 9

58,299 points

Apr 30, 2025 11:39 AM in response to bob7

Two comments...

> I discovered one design feature I would like

Use: Feedback - Numbers - Apple to provide feedback to the Numbers product team for possible new features and/or bug reports.

> Label and Summary Rows cannot be hidden

I disagree with this - at least the Label row can be hidden.

Select the Sum field in the row, and click the gear icon:

In the popup that appears, you can designate which calculation the cell should show. The last option in this menu is 'Show/Hide Label Row' which will toggle whether the label row is visible or not.

Reply

Answer 5

bob7 Author

Level 1

4 points

Apr 30, 2025 3:46 PM in response to Camelot

Yes, you're correct that Label Rows can be hidden. Thanks for that. Unfortunately not Summary Rows. I'll send feedback to Apple.

Reply