PurduefanGA

Q: How can I sum a column in numbers

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)

Posted on Nov 3, 2015 10:37 AM

Close

Q: How can I sum a column in numbers

  • All replies
  • Helpful answers

  • by SGIII,Solvedanswer

    SGIII SGIII Nov 3, 2015 1:42 PM in response to PurduefanGA
    Level 6 (10,627 points)
    Mac OS X
    Nov 3, 2015 1:42 PM in response to PurduefanGA

    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

  • by PurduefanGA,

    PurduefanGA PurduefanGA Nov 3, 2015 1:47 PM in response to SGIII
    Level 1 (8 points)
    Desktops
    Nov 3, 2015 1:47 PM in response to SGIII

    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.

  • by SGIII,Helpful

    SGIII SGIII Nov 3, 2015 2:26 PM in response to PurduefanGA
    Level 6 (10,627 points)
    Mac OS X
    Nov 3, 2015 2:26 PM in response to PurduefanGA

    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

  • by PurduefanGA,

    PurduefanGA PurduefanGA Nov 3, 2015 2:31 PM in response to SGIII
    Level 1 (8 points)
    Desktops
    Nov 3, 2015 2:31 PM in response to SGIII

    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.

  • by PurduefanGA,

    PurduefanGA PurduefanGA Nov 5, 2015 10:35 AM in response to PurduefanGA
    Level 1 (8 points)
    Desktops
    Nov 5, 2015 10:35 AM in response to PurduefanGA

    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

     

     

     

     

     

     

     

     

     

     

     

     

     

     

  • by SGIII,

    SGIII SGIII Nov 5, 2015 12:08 PM in response to PurduefanGA
    Level 6 (10,627 points)
    Mac OS X
    Nov 5, 2015 12:08 PM in response to PurduefanGA

    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

  • by Yellowbox,

    Yellowbox Yellowbox Nov 6, 2015 7:23 AM in response to SGIII
    Level 6 (10,435 points)
    Mac OS X
    Nov 6, 2015 7:23 AM in response to SGIII

    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:

    Screen Shot 2015-11-07 at 2.19.39 am.png

     

    Watch this space!

     

    Regards,

    Ian.

  • by SGIII,

    SGIII SGIII Nov 6, 2015 7:58 AM in response to Yellowbox
    Level 6 (10,627 points)
    Mac OS X
    Nov 6, 2015 7:58 AM in response to Yellowbox

     

    Watch this space!

     

     

    Hi Ian,

     

    Looking forward to that. Numbers tables really are versatile.

     

    SG

  • by Yellowbox,

    Yellowbox Yellowbox Nov 6, 2015 8:08 AM in response to SGIII
    Level 6 (10,435 points)
    Mac OS X
    Nov 6, 2015 8:08 AM in response to SGIII

    Hi SG,

     

    Ignore the values (the tables need more work) but here is where I started

    Screen Shot 2015-11-07 at 3.03.32 am.png

     

    Please feel free to write your own User Tip . Your knowledge of Excel will help those who are new to Numbers.

     

    Regards,

    Ian.

  • by Yellowbox,

    Yellowbox Yellowbox Nov 7, 2015 3:38 AM in response to SGIII
    Level 6 (10,435 points)
    Mac OS X
    Nov 7, 2015 3:38 AM in response to SGIII

    Hi SG,

     

    Stack tables to simulate an Excel sheet

     

    Plesse comment and edit.

     

    Regards,

    Ian.

  • by PurduefanGA,

    PurduefanGA PurduefanGA Nov 7, 2015 4:47 AM in response to SGIII
    Level 1 (8 points)
    Desktops
    Nov 7, 2015 4:47 AM in response to SGIII

    TThanks. The help you and yellow box are giving me is really great. 

  • by PurduefanGA,

    PurduefanGA PurduefanGA Nov 7, 2015 4:51 AM in response to Yellowbox
    Level 1 (8 points)
    Desktops
    Nov 7, 2015 4:51 AM in response to Yellowbox

    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. 

  • by Yellowbox,

    Yellowbox Yellowbox Nov 7, 2015 5:39 AM in response to PurduefanGA
    Level 6 (10,435 points)
    Mac OS X
    Nov 7, 2015 5:39 AM in response to PurduefanGA

    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