Currently Being ModeratedSep 4, 2012 7:57 PM (in response to gcm118236)
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:
Currently Being ModeratedSep 5, 2012 3:17 PM (in response to Wayne Contello)
Thank you. I tried what you suggest but the functions are not enabled. I've sanitized my document to take out personal information and shortened it so you can see the categories. What I am doing is an inventory for a shipment, so the columns are filled with details and explanations. The columns are as indicated in the screen shot below, minus my details. I can enable the "sum" for the total at the bottom, but not for any of the other rows, which I have left empty at the bottom of each category for the purpose of sub-totalling. Perhaps I need to create a special type of function row for this, but couldn't find out how to do this from my fumbling around. I am completely new at this software. Thank you very much for your help.
Place of Purchase
December 1 2012
ELECTRONICS, CABLES AND RELATED
1 + 10
$0.00iMac, Mac OS X (10.7.1)
Currently Being ModeratedSep 5, 2012 4:00 PM (in response to gcm118236)
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:
this notation means in cell E25 type "=SUM(E)" but omit the double quotes
Currently Being ModeratedSep 5, 2012 5:51 PM (in response to Wayne Contello)
Thanks again. I really appreciate your help. I tried to drag the screen shot into the thread, tried to copy it into the thread and finally just created a new sheet with the abbreviated version and Command C copied and Command V pasted into the thread. At least that worked. I have to figure out how to get the screen shot into the thread another time.
I can do the full "Total Value" as the formula appears to be included in the template in the line entitled "Total Value". In fact I just highlight and "sum" those columns. What I would like to figure out however is how to get a sub-total from each of the categories in the last line of the category ("clothing", "Electronics", "Various") and then have that totalled up for a grand total. How do you insert the code into each of those so the table will do that for me? Admittedly, I haven't tried with the new information you gave me and will do so now, so maybe there is an answer there, but they are servicing the thread at 10 pm, so I thought I'd respond to you first. Thanks again.
Currently Being ModeratedSep 5, 2012 6:27 PM (in response to gcm118236)
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:
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
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
Currently Being ModeratedSep 5, 2012 7:52 PM (in response to Wayne Contello)
Thanks, I'm working with it and like the concept. I'm having trouble, believe it or not, putting a title on top of the individual tables. How do I do this?
Currently Being ModeratedSep 5, 2012 8:36 PM (in response to gcm118236)
Currently Being ModeratedSep 5, 2012 9:13 PM (in response to gcm118236)
All Done. It seems a bit arduous, but I'm probably doing more than I need to. Thanks. I now have a Summary Table and separate Tables for each category. I had to enter the numeric values manually into the Summary table, which is not a big deal, but if they are named exactly as each table, I should be able to find a way to have them correspond without doing it manually. Whatever. Thank you for your help. Cheers!
Currently Being ModeratedOct 28, 2012 4:04 PM (in response to gcm118236)
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.
Currently Being ModeratedOct 24, 2013 8:31 PM (in response to SandiL_OR)
Thanks. Exactly what I was looking for. Appreciate that you took the time to post. .
Currently Being ModeratedOct 24, 2013 8:54 PM (in response to gcm118236)
" I have to figure out how to get the screen shot into the thread another time."
To take a screen shot:
- 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.