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")
  • Please edit your question to include some sample data, especially for column D.
    – Blindspots
    Apr 25 at 0:49

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

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.