Google-sheets – SUMIFS with OR criteria

google sheetsgoogle-sheets-query

I'm trying to write a sum function for all entries in a certain month (>= start of month, < eomonth + 1) where a certain field has one of several values.

(For example: All entries in July, where project = Project A or project = Project B)

So far, I've been using SUMIFS, but I can't figure out how to get the OR criterion in.

Example:

SUMIFS(E:E, C:C, ">="&B6, C:C, "<"&EOMONTH(B6, 0)+1, G:G, "=Project A“, A:A, "=Person B”)

The B column holds a particular month as a date. I'd like to use the OR criterion on people (e.g. "=Person B" OR "=Person C").

Best Answer

I found a solution to my problem:

Rather than using SUMIFS, I'm using a SUM of QUERY:

=SUM(QUERY(A:G,"Select E where A matches '"&Join("|","Person A", "Person B", "Person C")&"' and G='Project A' and toDate(C) >= date '" & text(B2, "yyyy-mm-dd") & "' and toDate(C) < date '" & text(EOMONTH(B2, 0)+1, "yyyy-mm-dd") & "' "))

The conditional part being where A matches '"&Join("|","Person A", "Person B", "Person C")&"'.

Related Topic