Is Numbers not able to subtotal spreadsheets? I used this feature all the time in Excel. Can't find it here.
MacBook Air (13-inch, Early 2014), OS X Yosemite (10.10.2)
Hi Chessie,
Sure you can. I don't quite know how you are trying to do it. a screenshot might help.
If you select the cells you wnat to total, you can drag the SUM marker from the bottom of the window to whereever you want it.
quinn
There is no SUBTOTAL function. Numbers '09 had a feature called Categories that could be used similarly but Numbers 3 doesn't have it.
But maybe you are referring to something different?
The function in Excel would start with a table of data and you could ask it to subtotal the data in all rows of one column, for every change in another column. For example, if you have a table of credit card charges, each with an associated date (column A), payee (column B), and amount (column C). You could SORT the table by payee, and then use the subtotal to show how much total was spent for each payee. The program would create a new row to show the subtotals below each unique payee, and show the overall total at the bottom. Make sense?
Numbers 3 does not have the equivalent of Excel subtotals or Pivot Tables. However, you can quite easily create separate tables using SUMIF, SUMIFS, COUNTIF, and COUNTIFS that summarize your data, and use these tables create charts. See the Personal Budget template ( File> New) for an example.
SG
Hi Chess,
Categories have gone in Numbers 3.
Payment table with a Header Row and a Footer Row
Formula in Footer Cell C9
=SUM(C)
Another table using the SUMIF function for subtotals for each payee
Formula in B2 (and Fill Down)
=SUMIF(Payments::B,A2,Payments::C)
Checksum in Footer Cell B6
=SUM(B)
You can sort the Payments table by any column and the Sub Totals table will still work.
Regards,
Ian.
Hi SG,
As usual, great minds think alike, (within 36 minutes, even though we are half a world apart 🙂).
Hi Chessie (the auto checker got it right this time) thanks for the gold star.
Regards,
Ian.
Hi Ian,
I would/should have written:
See the Personal Budget template ( File> New) and, even better, Ian's excellent illustration, for an example.🙂
Your use of Footer Rows with a SUM in each table is a great idea. If the sums match, all is well. If they don't, then check to see if a Payee is duplicated, or missing, or misspelled, etc.
SG
Hi SG,
I stole that SUMIF method from you 🙂.
Re: Group Rows into Categories gone in new Numbers
Regards,
Ian.
One of the most important (and simple) uses for the Subtotal function in Excel is to be able to sum a list of numbers which include base numbers and SUM calculations - the Subtotal function enables you to sum the entire list without including the other totals.
In the example below, in Excel you would use the Subtotal function for each of the Total rows, then the overall Expenses total at the bottom would Subtotal the full list and provide only the total of the Item values, not the previously calculated totals, using the following function.
=SUBTOTAL(9,B3:B16)
EXPENSES | $'000 |
Category A151 | |
Item A | 15 |
Item B | 6 |
TOTAL | 21 |
Category A157 | |
Item C | 2 |
Item D | 5 |
Item E | 3 |
Item F | 5 |
TOTAL | 15 |
Category B371 | |
Item R | 7 |
Item S | 3 |
Item T | 9 |
TOTAL | 19 |
EXPENSES TOTAL | 55 |
How on earth do you achieve the same in Numbers? At the moment I'm so frustrated with my new MacBookPro and with Apple Support who don't have anyone who can tell you anything about their software products that I'm thinking I'll sell it and go buy a Windows laptop! I hate the dumbed down versions of Windows out there, but trying to use software that doesn't even do basic functions is even worse.
One possible solution is shown below. Column C shows the formulas that are in column B. The final sum could have used "Total" as the condition vs "<>Total" except it would only sum the values that are included in a subtotal. In your table they amount to the same thing but in other tables you might have values that are not subtotaled.
Is Numbers not able to subtotal spreadsheets?
