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?
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.