1

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

  • What's an example of a formula you've been using? – freginold Jan 15 '18 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 '18 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 '18 at 10:04
1

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, privacy policy and cookie policy

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