mac - Excel: How to sum values in a column based on multiple criteria

20
2014-04
  • NinjaCat

    I have a list of criteria in row 20, like this:

    A20: "My Company, Inc."
    B20: "Client Expenses"
    C20: "Travel"
    

    The data is below, like this:

    B40: "Client Expenses"
    C40: "My Company, Inc."
    D40: "Travel"
    I40: $100
    
    B41: "Client Expenses"
    C41: "Acme, Inc."
    D41: "Travel"
    I41: $200
    
    B42: "Client Expenses"
    C42: "My Company, Inc."
    D42: "Food"
    I42: $300
    
    B43: "Client Expenses"
    C43: "My Company, Inc."
    D43: "Travel"
    I43: $400
    

    I want to sum all of column I, if the criteria in A,B,C: 20 matches the data in B,C,D: 40:43

    In OpenOffice Calc, this works:

    =SUMPRODUCT($C$40:$C$66=A27;$D$40:$D$66=C27;$B$40:$B$66=B27;$I$40:$I$66)
    

    In Excel for Mac OS X, it gives me $0. Can someone help me either fix this, or comes up with a different method to do the same thing?

  • Answers
  • Sux2Lose

    Wrap each argument in parenthesis and add two dashes preceding them. Here it is rewritten:

    =SUMPRODUCT(--($C$40:$C$66=A27),--($D$40:$D$66=C27),--($B$40:$B$66=B27),$I$40:$I$66)
    

    That should work.

  • AdamV

    Depending on your local language settings, you may need to replace the semicolons with commas.

    If you use commas as a decimal separator eg 123,45 (as in much of Europe) then semicolons usually are used for separating arguments in formulas.

    If you use a decimal point / period for decimals eg 123.45 then commas are used as arghument separators. So this might work:

    =SUMPRODUCT($C$40:$C$66=A27,$D$40:$D$66=C27,$B$40:$B$66=B27,$I$40:$I$66)
    

    Alternatively, try using SumifS (a newer function to do SUMIF with multiple criteria) or possibly DSUM (which would wasily allow you to add "OR" conditions as well as "AND", for example:

    Company = "Acme" AND (Expense type = "Travel" OR "Food")

  • Ricardo

    Did replacing the semicolons for commas fix this for you?

    You could also convert your table into a Pivot Table and filter your criteria as you wish. Your case seems simple enough to learn how to use Pivot Tables, in case you don't already know. It's as easy as dragging and dropping relevant criteria in a box.


  • Related Question

    microsoft excel - Sum Values in One Range Based on Criteria in Another
  • Tom Gullen

    I have a simple spreadsheet, two columns "Amount" (col Q) and "Account" (col O). I want to get the sum of the "Amount" column where the corresponding "Account" forum equals "Paypal".

    This is as far as I can get!

    =SUM(Q:Q)*(O:O="Paypal")
    

  • Related Answers
  • Mokubai

    I believe rather than using SUM() you want the SUMIF() function. I don't have Excel to hand but I believe you need your formula to be

    =SUMIF(O:O,"Paypal",Q:Q)
    

    From this site

    The syntax for the SumIf function is:

    SumIf( range, criteria, sumRange )
    

    range is the range of cells that you want to apply the criteria against. (your column containing "Paypal"

    criteria is used to determine which cells to add. - In your case matching "paypal"

    sumRange are the cells to sum.

    Also see this Microsoft help page, and this page which has some more advanced functions such as DSUM and SUMPRODUCT.