I add rows into various places in a numbers spread sheet and want several column totals. But I have to edit the total fields every time I add a row.
Is there anyway to have a function called "sum this column" so it doesn't matter how many rows there are to be summed?
iMac, OS X Yosemite (10.10.5)
Define the row with sums as a Footer Row. Then the sum formulas can refer to the entire column, e.g. =SUM(A) =SUM(B) etc.
SG
Thanks so much and thanks for the quick answer. One odd thing did happen though. I defined the row as a footer, then I used the function sum and it properly added the column data for all but one total, and it summed the row, not the column, yet totals before and after worked fine summing the column, So what did I do or not do that this one total decided to row on all the pervious numbers in that footer row? (I know this would be useful in some cases where you want column totals then the grand total across the row. Unfortunately that wasn't my case.)
But I never knew about footers usefulness before. Thanks.
PurduefanGA wrote:
One odd thing did happen though. I defined the row as a footer, then I used the function sum and it properly added the column data for all but one total, and it summed the row, not the column, yet totals before and after worked fine summing the column, So what did I do or not do that this one total decided to row on all the pervious numbers in that footer row?
Hi PurduefanGA,
It's hard to know what went wrong with that one total without seeing a screenshot of how your table looks and the formula in the cell. Perhaps you can delete that formula and try entering it again. Footer rows can be really useful because using them can simplify your formulas and also make it so you don't have to worry about adjusting them every time you add a row to your table.
Thanks for the green tick!
SG
YYou deserve the green check as you have definitely saved me lots of time fixing formulas. Except that one, which I did delete and redid. Maybe I need to make sure all the numbers in the column have identical formatting. Although they do sum correctly . I must pass on a screen shot though. The solution is excellent for everything but one and I can live with that.
I created a dummy numbers table, and I am trying to have the two total rows sum no matter how many rows I put between them and their header. I seem OK on the first total row(currently row 7) but on the second total row (currently row 13) it wants to sum the entire column beginning with row 2. So is there a way for
the first total row to start at row 2 and end at the row containing the word "total", and the second " total IRA " to start summing after the label "Stock from IRA" ending at the row above the" total IRA "row?
In other words I would like to add rows in each of the parts of the table without redoing all of the total rows.
I know it isn't hard to redo the formulas in this simple example, but i have several more sections and several more columns to sum. And in each section I add rows.
Thanks for your advice.
stock | qty | price | total | current price | total | gain loss |
ABC | 100 | $20.00 | $2,000.00 | $21.00 | $2,100.00 | $100.00 |
DEF | 100 | $130.00 | $13,000.00 | $129.00 | $12,900.00 | -$100.00 |
XYZ | 200 | $35.00 | $7,000.00 | $38.00 | $7,600.00 | $600.00 |
HHH | 100 | $10.00 | $1,000.00 | $15.00 | $1,500.00 | $500.00 |
JJJ | 100 | $85.00 | $8,500.00 | $84.00 | $8,400.00 | -$100.00 |
total | 600 | $31,500.00 | $32,500.00 | $1,000.00 | ||
Stock From IRA | qty | price | total | current price | total | gain loss |
ABC | 100 | $35.00 | $3,500.00 | $38.00 | $3,800.00 | $300.00 |
ABC | 200 | $33.00 | $6,600.00 | $38.00 | $7,600.00 | $1,000.00 |
XYA | 50 | $40.00 | $2,000.00 | $40.00 | $2,000.00 | $0.00 |
total IRA | ||||||
Grand Totals | 600 | $31,500.00 | $32,500.00 | $1,000.00 | ||
PurduefanGA wrote:
but i have several more sections and several more columns to sum. And in each section I add rows.
Since in the future you think you'll add rows in each section, why not put each section in its own separate table, with a Footer Row? Then it's a simple matter to format the tables and put them in a position so that they look like one big table.
SG
Hi SG,
Your reply:
Since in the future you think you'll add rows in each section, why not put each section in its own separate table, with a Footer Row? Then it's a simple matter to format the tables and put them in a position so that they look like one big table.
has inspired me to compose a User Tip for getting the best out of Numbers with several tables (with Headers and Footers) that simulate the "defined areas" of an Excel Sheet.
Sneak preview of three tables:
Watch this space!
Regards,
Ian.
Watch this space!
Hi Ian,
Looking forward to that. Numbers tables really are versatile.
SG
Hi SG,
Ignore the values (the tables need more work) but here is where I started
Please feel free to write your own User Tip 🙂. Your knowledge of Excel will help those who are new to Numbers.
Regards,
Ian.
TThanks. The help you and yellow box are giving me is really great.
THANKS! I truly appreciate the help on this. I have several spreadsheets that this will be a huge help in doing column sums. I have made mistakes in the past that multiple tables will avoid.
Hi PurduefanGA,
Full credit to SG for the idea:
Since in the future you think you'll add rows in each section, why not put each section in its own separate table, with a Footer Row? Then it's a simple matter to format the tables and put them in a position so that they look like one big table.
Also credit SG for editing the User Tip with superior knowledge of how Excel works. Not "defined areas" but Named Ranges.
I see that SG is being modest 😎, and edited out my acknowledgement from the User Tip.
Regards,
Ian
How can I sum a column in numbers
