Q: Is Numbers not able to subtotal spreadsheets?
-
Feb 6, 2015 5:38 PMby Chessie111,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?
-
Feb 6, 2015 11:18 PMby SGIII,HelpfulNumbers 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
-
Feb 6, 2015 11:54 PMby Yellowbox,HelpfulHi 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.
-
Feb 7, 2015 7:35 AMby SGIII,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
-
Nov 13, 2015 3:24 PMby MusicbyTeo,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.
-
Nov 13, 2015 5:02 PMby Badunit,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.
