CODEWITHSUNDEEP
excelsumifs
Jonas Palačionis
Jonas Palačionis

Reputation: 4842

Excel sumifs with two values throwing an error

I am trying to get a sum of column C if A is abc or cba and B is def:

=SUMIFS(C2:C51;A2:A51;{"abc","cba"};B2:B51;"def")

But the formula is not valid, not sure where is my mistake since this was proposed in a quick google search.

Thank you for your suggestions.

Upvotes: 0

Views: 137

Answers (2)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

Probably I would use @Plutian answer (actually I upvoted), but in case it might work for you, you can use SUMPRODUCT combined with DOUBLE UNARY to get exactly what you want.

DOUBLE UNARY

SUMPRODUCT

I made a fake dataset like this:

enter image description here

As you can see, only the highlighted values meet your requirements ( if A=abc OR cba AND B=def)

My formula in E10 is:

=SUMPRODUCT(--($A$2:$A$7="abc")+--($A$2:$A$7="cba");--($B$2:$B$7="def");$C$2:$C$7)

This is how it works:

  1. ($A$2:$A$7="abc") will return an array of True/False values if condition is met.
  2. That array, because it's inside a double unary operator --( your range ), will convert all True/False values into 1 or 0 values. Let's say it works like if you would have selected a range of cells that contains only 1 or 0. So this will return an array like {1,0,1,0,1,0} in this case
  3. --($A$2:$A$7="cba") will do exactly the same than steps 1 or 2 again, but with your second option. It will return another array of values, in this case, {0,1,0,1,0,1}
  4. --($A$2:$A$7="abc")+--($A$2:$A$7="cba") we are just summing up both arrays, so {1,0,1,0,1,0}+{0,1,0,1,0,1}={1,1,1,1,1,1}
  5. --($B$2:$B$7="def") will do like steps 1 and 2 again with your third condition, and will return another array, now it will be {1,0,1,0,0,1}
  6. The array obtained in step 5 then it's multiplied to array obtained in step 4, so we are doing {1,1,1,1,1,1} * {1,0,1,0,0,1}={1,0,1,0,0,1}
  7. Now, that final array obtained in step 7 then it's multiplied by the values of cells $C$2:$C$7, so in this case is {1,0,1,0,0,1} * {10,1,10,1,1,10} = {10,0,10,0,0,10}
  8. And final step, we sum up all values inside array obtained in last step, so we do 10+0+10+0+0+10=30

I've explained every step to make sure everybody can understand, because SUMPRODUCT it's really an useful function if you know how to hanlde (I'm a noob, but I've seen real heroes here on SO using this function).

The advantage of using SUMPRODUCT instead of SUMIFS is that you can easily add more conditions to apply same range (case --($A$2:$A$7="abc")+--($A$2:$A$7="cba") or single condition to additional ranges (case --($B$2:$B$7="def")).

With normal SUMIFS probably you would have to add 1 extra complete SUMIF for each condition applied in same range.

Hope this helps

Upvotes: 2

Plutian
Plutian

Reputation: 2309

The formula is valid for me, but this might be an issue with your delimiter. Depending on your excel, windows or location settings you might need to use a comma , as a delimiter, instead of a semicolon ;.

As for your formula, for completion I've done the same google search and ended up with this reference. It seems your logic in the formula is correct apart from one crucial step, the SUM( wrapping around your formula. This means if your formula works, it will only take the first hit into account, but with the sum, it will count every entry where your logic is True. Syntax:

=SUM(SUMIFS(C2:C51,A2:A51,{"abc","cba"},B2:B51,"def"))

Or semicolon delimited:

=SUM(SUMIFS(C2:C51;A2:A51;{"abc";"cba"};B2:B51;"def"))

Since the {array} option does not seem to be working for you, I propose a workaround as follows:

=SUMIFS(C1:C15;A1:A15;"abc";B1:B15;"def")+SUMIFS(C1:C15;A1:A15;"cba";B1:B15;"def")

This is a more clunky function, but reaches the same result by splitting up the data in two SUMIFS( functions and adding the results together.

Upvotes: 2

Related Questions