Search code examples
excelsumifs

SUMIFS with multiple criteria and OR logic in multiple columns

I found here an example for doing a sumifs with multiple criteria and OR logic, but I tried with multiple to do it for multiple columns and it is not working.

Here is an example. Base on this dataset:

A1  B1  C1  D1
B   X   u   11
B   X   u   22
A   X   t   22
B   X   t   22

I'm using the following formula:

=SUM(SUMIFS(D:D,B:B,"X",A:A,{"A","B"},C:C,{"t","u"}))

I get 55 as a result in the formula, but it should be 77 instead

If I change last row of the dataset it calculates correctly like:

A1  B1  C1  D1
B   X   u   11
B   X   u   22
A   X   t   22
A   X   t   22  --> Column A1 value changed from B to A

Is it possible to have multiple columns with multiple OR values?

Solution

  • Try,

    =SUM(SUMIFS(D:D, B:B, "X", A:A, {"A","B"}, C:C, TRANSPOSE({"t","u"})))

    By changing the 'direction' in which the second array of criteria is read, you get all possible combinations instead of paired combinations.