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

---