Search code examples
excelsumifsarray-formulas

SUMIFS with and + or criteria in Excel

I would like to sum all values in C where column A is in {"A", "B"} and column B is in {"W", "X"} -- this is the orange records. It is the "overlap" of the two conditions. The answer should be 8. I thought the SUM(SUMIFS( approach would work, but it does not.

Picture

Solution

  • It will work but one of the arrays must be horizontal and the other vertical:

    =SUM(SUMIFS(C:C,A:A,{"A","B"},B:B,{"W";"X"}))

    The , makes it horizontal and the ; vertical. The limit is two arrays a third can not be added.

    enter image description here

    To add more than two one must switch to something else.

    One can use SUMPRODUCT, with ISNUMBER(MATCH()) for each Criteria:

    =SUMPRODUCT(ISNUMBER(MATCH(A1:A9,{"A","B"},0))*ISNUMBER(MATCH(B1:B9,{"W","X"},0))*ISNUMBER(MATCH(C1:C9,{"D","E"},0)),D1:D9)

    enter image description here