Search code examples
arraysif-statementfilterexcel-formulasumifs

Excel: SUMIF column A = x and (y or z)

Here is an example of the data I am using.

0 |      A      |    B
1 | Information |   Time  
2 | ----------- | ---------
3 |   x y abc   |    3
4 |   x z abc   |    4
5 |   y z abc   |    5

I would expect this to return the value 7.

I understand SUMIF(B:B,A:A,"x") and using SUMIF for multiple criteria but I am struggling to figure out how to integrate the ability for an and/or SUMIF.

DATA TABLE

50e flute dolley tripped twice          |   0.29
dolly tripped paser flute               |   0.21
No 5 tripeed. Waiting for sparky.       |   0.18
No 2 tripped - waiting for sparky.      |   0.14
No 2 dolly tripped - waiting for sparky |   0.09
Dolly tripped, waiting for sparky       |   0.08
No 2 dolly - waiting for sparky         |   0.08
50e flute dolly                         |   0.07
dolly tripped 50e flute                 |   0.06
no 2 dolley tripped                     |   0.06
50e flute dolley (side 1) tripped       |   0.06
No 2 - waiting for sparky               |   0.06

I want 5 different functions for the 5 different reel dollies. So for number 5, I want to ensure I am only counting the no 5 dolly and not the 50e flute dolly. This is a dataset entered external so I want to be able to cover criteria that includes wrongly spelled words and multiple names for a particular reel dolly.

Solution

  • You might be looking for this:

    =SUM(SUMIFS(B:B,A:A, "*x*",A:A, {"*y*","*z*"}))

    Which means to sum column B *where A contains "x" and either of "y" and "z".

    The logic in this construction is:

    • mandatory criteria come alone

    • "ORed" criteria come inside the {...} array.

    Example, dolley no 2:

    =SUM(SUMIFS(B:B,A:A, "* 2 *",A:A, {"*dolly*","*dolley*", "no *"}))