Search code examples
excelgoogle-sheetssumifs

SUMIFs in Google sheets with 2 filters on same criteria range

I have looked at various answers but all use filters on different columns. I need an OR type filter like this:

=SUMIFs(B2:B22,C2:C22,"Incurred",C2:C22,"Expected")

Where C2:C22 specifies if the cell in B is an incurred or expected or deferred expense.

How do I do this? Note the filters are on the same set of cells in column C.

Solution

  • This is the data in A6:C6:

    1   A   C
2   B   D
3   A   D
4   B   C
5   A   D
6   B   D

    To sum the values if column B is A or column C is D you can use:

    =SUM(ARRAYFORMULA((IF(((B1:B6="A")+(C1:C6="D"))>0,A1:A6))))

    This uses the fact that True equals 1 and False equals 0 once you want to use them in equation.