1

I want a sheet that counts across multiple columns

Here's my Google Sheet

H           M       P       U
Submitted   3/3/23  John    Validation
In progress         John    In progress

I want to use a single COUNTIFS function that counts all rows where all these conditions are true:

  1. the date value in column M is more recent than 1 month ago OR the cell value of column M is empty
  2. value in column P is "John"
  3. value in column U is not "Closed - Lost" and value in column U is not "Closed - Active"
  4. value in column H is not "Submitted".

I would expect my function to return 1.

Here's what I tried, but it returns N/A or 0 respectively.

=COUNTIFS(RFX!M:M, "<="&EDATE(TODAY(), -1), RFX!P:P, "John", RFX!U:U, "<>"&"Closed - Lost", RFX!U:U, "<>"&"Closed - Active", RFX!H:H, "<>", OR(RFX!M:M, RFX!M:M = ""))

=COUNTIFS(RFX!M:M, "<="&EDATE(TODAY(), -1), RFX!M:M, "<>", RFX!P:P, "John", RFX!U:U, "<>"&"Closed - Lost", RFX!U:U, "<>"&"Closed - Active", RFX!H:H, "<>", RFX!H:H, "<>"&"Submitted")

In my solution I want no (or as little repetition) of conditions as possible to reduce maintenance effort.

What am I doing wrong?

1 Answer 1

2

The easiest way to avoid repetition is probably to use query(), like this:

=ifna( query( 
  RFX!H:U, 
  "select count(H) 
   where H <> 'Submitted' 
   and P = 'John' 
   and not U contains 'Closed' 
   and (M is null or toDate(M) <= date " & text(edate(today(), -1), "'yyyy-MM-dd'") 
   & ") label count(H) '' ", 
  0 
) )

See query().

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.