how do you create a sub-total in numbers for each category

2355 Views 11 Replies Latest reply: Oct 24, 2013 8:54 PM by Barry
Calculating status...
Currently Being Moderated
Sep 4, 2012 7:34 PM

I'm new to Numbers and would like to add up columns into sub-totals for each category.  I've fumbled about a bit with help and formulas, but cannot easily find how to add in the necessary row or formula at the end of each category.  The template gives me one total line at the end, which I can manage, but how do I separate out the sub-total formulas for each sub-category and then direct it to add the sub-totals into the final total (without double-counting the sub-total on top of the individual amounts)?  Thank you for your help.

iMac, Mac OS X (10.7.1)
• Level 6 (12,620 points)

can you post what a screenshot of what you have so far?

Generally you can use the sumif() function.  The function browser has a detailed description of this function.  To open the function browser select the menu item "View > Show Function Browser".

There is also a free, downloadable, Users' Guide located here:

http://support.apple.com/manuals/#productivitysoftware

• Level 6 (12,620 points)

Posting a table like this is great.

Just so you know the table you posted is NOT a screen shot.  There are several threads in this forums for how to take a screenshot.

Are you wanting totals for each category, then a grand total?  Or do you want a total for columns titled "Original Cost" and "Replacement Value"?

I assume you want total for columns titled "Original Cost" and "Replacement Value"?:

first... add a footer row by using the table inspector (headers and footers are NOT included in sums):

remove the contents of the current last row:

In the footer row add the formulas:

E25=SUM(E)

F25=SUM(F)

this notation means in cell E25 type "=SUM(E)" but omit the double quotes

• Level 6 (12,620 points)

Based on what I know now I recommend a separate table for each category and a single table for a summary.

It would look like:

Overview:

The Clothing Table:

Once you have the "Clothing" table set up... duplicate it by selecting the table, then select the menu item "Edit > Duplicate".

To set up the Clothing table

1) make the first row a header

2) add a footer at the end

3) the footer is row 10 in the picture above

E10=SUM(E)

select E10, copy, then select F10 and paste

Name the table by clicking on the name of the table in the sheet viewer on the left, edit the name and hit enter to exit editing.  Now select the table check the "Name" checkbox in the tool bar.

now duplicate the table once for each category.

Name each table by clicking on the name of the table in the sheet viewer on the left, edit the name and hit enter to exit editing.

Now create a new single table and name it "Summary":

Make the first row a header and add a footer in the last row.

B2=IFERROR(SUM(INDIRECT(\$A2&" :: E")), "")

C2=IFERROR(SUM(INDIRECT(\$A2&" :: F")), "")

select B2 and C2, then fill down as needed

The names in the "Category" column (column A) must match the names of the category tables

• Level 6 (12,620 points)

Name the table by clicking on the name of the table in the sheet viewer on the left, edit the name and hit enter to exit editing.  Now select the table check the "Name" checkbox in the tool bar.

• Level 1 (0 points)

I found this when searching an answer for the same question. It's actually easier than this. It looks like you probably don't need this answer any longer, so I'm posting this here in case someone else is searching for this answer (as I was) and comes across this.

Select the category row that you want to subtotal, say, clothing. Select the cell that is *above* the column of numbers that you want to subtotal, but in the category heading row. For instance, in the category for CLOTHING row, select the cell under the column heading ORIGINAL COST. I cannot see your row and column headings in your example, so hopefully that makes sense.

When you select that cell, there will be a triangle that appears in the right side of the cell. Select the triangle and a drop-down menu appears. Select "subtotal" from the drop down menu and all the categories will now have a subtotal in their heading row for that column in each category.  It's kinda strange because the subtotal appears ABOVE the column of numbers that it is subtotaling, but at least it's a simple way to subtotal all the categories.

• Level 6 (11,550 points)

Thanks.  Exactly what I was looking for.  Appreciate that you took the time to post.  .

--Kirby.

• Level 7 (29,095 points)

" I have to figure out how to get the screen shot into the thread another time."

To take a screen shot:

Partial screen:

• Place mouse pointer at top left corner of area you want to capture.
• Press command-option-4 (pointer chenges to a crosshair).
• Press and hold the mouse button.
• Drag a selection rectangle to enclose the area you want to capture.
• Release the mouse button.
• The screen shot will be saved to your desktop with the name "Picture n" where n is a serial number from 1 to 10, OR with the name "Screen Shot" followed by the date and time the capture was done. (The names depends on which version of OS X you are using.)

Posting the screen shot here:

• Click the camera icon in the toolbar above the compose box.
• Click Choose File
• Locate and select (click) the file.
• Click Choose.
• Click Insert file.

• (note: You may need to click Cancel and repeat these steps on the first time through after signing in. The camera sometimes takes a nudge to wake up.

To take a shot of the full screen (not recommended for posting here):

• Press shift-command-3
-The shot is immediately saved to the desktop.

To store the shot on the Clipboard (for pasting into a document, not to this formum.):

• Add the option key to either of the screen shot keystroke combinations.
-Full screen: shift-option-command-3
-Partial screen: shift-option-command-4
• continue as described above.

Regards,

Barry

More Like This

• Retrieving data ...

Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.