2 Replies Latest reply: Feb 7, 2014 4:48 PM by SGIII
ramone kalsaw Level 1 Level 1 (0 points)

Hello … I’ve added a row in the spreadsheet below for 'sales tax' per transaction … I want to keep a running total of sales tax paid to date … since I don’t have a specific column set up for showing a simple sum … any suggestions for a formula for aggregating all 'Sales Tax' entries so that they show up in a cell in my current 'Totals' table?

 


Thanks


 

Numbers.png


 


MacBook Pro (Retina, 15-inch, Late 2013), OS X Mavericks (10.9.1)
  • Barry Level 7 Level 7 (29,465 points)

    HI Ramone,

     

    SUMIF should do the trick.

     

    Assuming the Category column is column C, the Purchases... column is column D, and these are both on a Table named "Main", place the formula below in the cell (on your Totals table) as shown. Note that the totals calculated are for rows where Category matches the value in the cell to the left of the Total cell:

    Screen Shot 2014-02-07 at 4.47.12 PM.png

    Totals::B2: =SUMIF(Main :: C,A2,Main :: D)

     

    To eliminate the 0.00 in cells where no category has been named, you can add this to the formula:

     

    Totals::B2: =IF(LEN(A)<1,"",SUMIF(Main :: C,A2,Main :: D))

     

    (original formula shown in Bold, added parts in regular.

     

    Regards,

    Barry

  • SGIII Level 5 Level 5 (6,825 points)

    Assuming Category is in column B (can't tell from screenshot) you could do something like this:

     

     

    Screen Shot 2014-02-07 at 7.44.06 PM.png

     

    My source table is 'Table 1-1'. Yours would be different.

     

    The formula is:

     

    = SUMIF('Table 1-1'::B,A4,'Table 1-1'::C)

     

    It says add up all values it finds in column C of the source table where the value in the Category column of the source table is the same as the value in A4 of the Summary table.

     

    SG