Make a summation in Excel by several criteria

Question

There is a table with data:

We want the numbers in the third column to add up when the value in the first column was "Red" or "White" and the value in the second column was "One" or "Two". Under such conditions, the sum should be 14 (1, 3, 4 and 6 lines are added). I tried this formula:

=SUM(SUMIFS(F$36:F$43;E$36:E$43;{"One";"Two"};D$36:D$43;{"Red";"White"}))

But for some reason the result of this formula is 7 instead of 14. Please help.

Answer 1

Nice question. It needs to be like this in order to return 14 instead of 7.

---

=SUM(
    SUMIFS(
        F$36:F$43,
        E$36:E$43, {
            "One";
            "Two"
        },
        D$36:D$43, {
            "Red",
            "White"
        }
    )
)

---

To check for the multiple conditions the criteria needs to validate both One & Two for Red & White on doing it returns an array of 2x2, first gets the value of Red for One & Two as {1;4} and then for the latter as {3;6}

---

=SUMIFS(F$36:F$43,E$36:E$43,{"One";"Two"},D$36:D$43,{"Red","White"})

---

In your version of Excel it will be:

=SUM(SUMIFS(F$36:F$43;E$36:E$43;{"One";"Two"};D$36:D$43;{"Red"."White"}))

---

Answer 2

I'm not sure if I'm explaining it correctly but when you use the curly brackets, you're specifying a range to which the sumif will look at the combination of them, i.e.
SUMIFS(F1:F9;D1:D9;{"Red";"White"};E1:E9;{"One";"Two"})
Will make the combination of Red+One and White+Two = 1 | 6 (see H1:H2) In my example I1:I2 is the result of the same formula but One and Two switched around. (this is at least what I understood from the result, will edit if I'm proven wrong)

A possible solution would be combining these two formulas in your final Sum formula:

=SUM(SUMIFS(F$36:F$43;E$36:E$43;{"One";"Two"};D$36:D$43;{"Red";"White"})+SUMIFS(F$36:F$43;E$36:E$43;{"Two";"One"};D$36:D$43;{"Red";"White"}))

This explains more why you got the result you had and a circumventing solution. I'm sure there's a better/cleaner way but I'll leave that for the more advanced excel-formula users.

Answer 3

Try this formula with SUMPRODUCT:

=SUMPRODUCT((C1:C9)*(ISNUMBER(MATCH(B1:B9,{"one";"two"},0)))*(ISNUMBER(MATCH(A1:A9,{"Red";"White"},0))))

(ISNUMBER(MATCH(B1:B9,{"one";"two"},0))) returns per row true or false. Same for the second condition.

* acts as an AND operator.