Single Google Sheet COUNTIFS function that counts across multiple columns with multiple conditions

Question

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?

Answer 1

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