Search code examples
excelfilterexcel-formulasumsumifs

Make a summation in Excel by several criteria

There is a table with data:

enter image description here

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.

Solution

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

    enter image description here

    =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}

    enter image description here

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