1

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?

Selrac
  • 2,203
  • 9
  • 41
  • 84

2 Answers2

2

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.

2

I was working on the same lines as @Jeeped but came up with

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

in order to change the direction of the second array.

Note: I don't know how you would easily extend this to include another set of criteria because you need another 'dimension'. As far as I can see it would have to look like this

=SUM(SUMIFS(D:D,B:B,{"X","X","X","X","Y","Y","Y","Y"},A:A,{"A","A","B","B","A","A","B","B"},C:C,{"t","u","t","u","t","u","t","u"}))
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • 1
    You're correct in that you cannot include a third set of criteria, since, as you say, you would require an extra 'dimension', which is beyond the capability of such functions. https://excelxor.com/2014/09/28/countifs-multiple-or-criteria-for-one-or-two-criteria_ranges/ – XOR LX May 22 '18 at 11:27
  • Is there a formula alternative? – Selrac May 22 '18 at 11:59
  • 1
    It's fairly well known that you can also do this with an array (or sumproduct) formula by writing * instead of AND, + instead of OR, so the above would become =SUMPRODUCT(D2:D5*((C2:C5="u")+(C2:C5="t"))*((A2:A5="A")+(A2:A5="B"))). You can create 'boolean' expressions of arbitrary complexity - see http://dailydoseofexcel.com/archives/2004/12/04/logical-operations-in-array-formulas/ – Tom Sharpe May 22 '18 at 14:12