Countifs N Count across multiple criteria (in data validations) but not returning zero if data validated cell is blank

Question

Trying to set up a formula where I can pull N counts based off multiple criteria including a date range. A typical Countif formula could work in theory:

=countifs(D2:D, J2, F2:F, J3, E2:E, J4, G2:G, J5, C2:C, ">="&TODAY()-7, C2:C, "<="&TODAY())

However, the problem I am facing is I want to be able to still pull the N count even if I leave one or more data validated cells the formula is pulling off of blank. In order to pull anything, you have to select one of the 4 data validated options. As you select more, the data gets more granular. Currently if I leave one cell blank, I then get a zero on the return. Need that not to happen

Any thoughts on how I could make this work?

I have done this with queries before, but I am not sure how to pull N count if use query. Happy to go that route as well if its easiest

Answer 1

use:

=COUNTA(IFNA(QUERY(C:G, 
 "select C 
  where 1=1 "&
 IF(J2="",," and D = '"&J2&"'")&
 IF(J3="",," and F = '"&J3&"'")&
 IF(J4="",," and E = '"&J4&"'")&
 IF(J5="",," and G = '"&J5&"'")&
 " and C <= date '"&TEXT(TODAY(),   "yyyy-mm-dd")&"'
   and C >= date '"&TEXT(TODAY()-7, "yyyy-mm-dd")&"'", 0)))