This question is about Numbers. I have a column of numbers with a sum cell at the bottom of the column. Sometimes I need to insert a row at the very bottom of the column, just above the cell with the Sum formula. I then need to tweak the sum formula to include that added row; however, if I add a row in the middle of the column, the sum formula automatically changes to include that added row.
iMac, OS X Mountain Lion (10.8.5)
Hi c,
Numbers will automatically adjust the formula if all body cells above the added cell have the same formula, or if the added cell is within the range specified in the formula.
In the middle of the column, the second is true. At the end of the column, the added cell is outside the range specifired in your formula, and there is at least one regular cell above that does not contain the same formula.
Use one or more header rows at the top of the table for your labels. Use a Footer row to hold the SUM formula. Place the SUM formula in the Footer row, using =SUM(B) for example to SUM the values in all of the body cells in column B.
Regards,
Barry
Thank you for your reply. I had seen that solution regarding using header and footer rows, but I have multiple sections in my spreadsheet for Which I need sums. I did find the answer however ...instead of adding my new row 'above' the 'sum' row, if I go to the last row in the section to be totaled and add a row 'below' the last row in the section, my sun formula then changes to include that added row! Yeah! Just wish that I could have found that answer easier in the user guide or somewhere. Basically found it by trial and error. Admittedly I was a hard-core Excel user!
Whoops sum formula, not sun formula
It's good practice to separate your data into groups with a table for each group. Where this doesn't make sense, the next best thing is to have a separate summary table with the subtotals. Of course this all depends on how many groups there are, and so forth.
You probably see now that the SUM range argument will adjust itself if you add a row from within the range, but not when initiated from outside the range. It's a logical behavior.
Jerry
In Numbers the way to go is break your "sections" into separate tables and put each SUM in a Footer Row as Barry suggests.
If you are hard set in your Excel ways, then the old Excel trick I used (actually still use sometimes) was to have a blank row (with a reduced height) above the row with the SUM formula and have the SUM formula include that blank row in its range. Then when "adding" a row, just insert it above that blank row.
But still, in Numbers, why not take advantage of the power of its individual tables? That's one of its main charms vis-a-vis Excel.
SG
Thanks so much for all of the help - yes, I am still kind of set in my Excel ways, but am learning to appreciate the differences! Now I just wish that Numbers had the "precision as displayed" capability.
sum formula doesn't change when add row
