How do I exclude a text value from a count in a Google Sheets query statement?

Question

I have gotten an output that works for my sum, but I need to exclude one value in count(D). I want to exclude from the count in column D all cells containing the text ZERO. I've been researching and cannot seem to find the solution. (ZERO is 0.00 in column C.)

=QUERY(A4:J,"Select H, sum(C), count(D) Where H is not null group by H")

Answer 1

You'll need to modify the data before running the QUERY. This formula replaces the word "ZERO" with a blank value.

=QUERY({C4:C, H4:H, INDEX(IF(D4:D="ZERO",, D4:D))},
   "Select Col2, sum(Col1), count(Col3)
    Where Col2 is not null group by Col2")