3

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").

3
  • What's an example of a formula you've been using?
    – freginold
    Jan 15, 2018 at 14:17
  • =SUMIFS(E:E, C:C, ">="&B6, C:C, "<"&EOMONTH(B6, 0)+1, G:G, "=Project A“, A:A, "=Person B”)
    – Tin Man
    Jan 16, 2018 at 10:03
  • 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")
    – Tin Man
    Jan 16, 2018 at 10:04

1 Answer 1

2

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")&"'.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.