Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
It would be useful to know which version of Excel you have, later versions (2007 onwards) introduced new functions such as SUMIFS which are ideally suited to solving your problem. –  AdamV Aug 4 '10 at 8:55
    
It's 2008 (v12), for Mac. However OpenOffice doesn't support SUMIFS yet, so ideally the solution would work on both. –  NinjaCat Aug 4 '10 at 9:28
    
If I do a nested if statement, I get #Value! which I don't understand... Col I is just a number field. =SUM(IF($C$40:$C$67=A27);IF($B$40:$B$67=B27;IF($D$40:$D$67=C27;$I$40:$I$67;0);0)‌​;0) –  NinjaCat Aug 4 '10 at 9:32
    
Your formula references A27, but you say the criteria is in row 20. –  dkusleika Aug 4 '10 at 16:16
    
Yeah, I have multiple lines of this... so to edit my comment above: =SUM(IF($C$40:$C$67=A20);IF($B$40:$B$67=B20;IF($D$40:$D$67=C20;$I$40:$I$67;0);0)‌​;0) –  NinjaCat Aug 4 '10 at 17:29
add comment

3 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
Inddeed... I tested it in OO. Now going to try the Mac version. –  NinjaCat Aug 4 '10 at 17:41
    
Did it work as expected? –  Sux2Lose Aug 6 '10 at 14:56
    
Indeed! Thanks... –  NinjaCat Aug 7 '10 at 21:22
add comment

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")

share|improve this answer
    
OpenOffice uses semicolons for separating function arguments whereas Excel uses commas. When you save the document into Excel, it converts them automagically. –  NinjaCat Aug 4 '10 at 9:29
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.