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

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?

-
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

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.

-
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

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

-
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

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.

-