I want to count how many paid is for city MB and then how many paid is for city UB.

I tried to use COUNTIFS and =COUNTA((QUERY(C4:C42, "select D where D='paid' and C='MB'", 0))) but I cannot make it work.

Somebody got an idea?

enter image description here

1 Answer 1


Since you have already tried the query solution, please use these two formulas:

in cell B2:

=query(D6:E10, "select count(D) where D='paid' and E='MB' label count(D) ''")

in cell D2:

=query(D6:E10, "select count(D) where D='paid' and E='UB' label count(D) ''")

You can read more about how the QUERY function works in the help center.

Note: In the end of the formula we use label count(D) ''. That translates to "take the label of the results in column D and replace it with nothing. Try omitting that part of the formula and see what happens ".

  • works perfectly. Thank you..... Would you know how to get rid of #N/A if there is no data, no match?
    – Radek
    Oct 9, 2019 at 5:48
  • I don't see why it would give you #N/A. If there is no data/match the query would have a red line under it saying "Query completed with an empty output". You must be referring to a different formula. Please share a link and maybe start another question. In any case the usual way is to use IFERROR. Oct 9, 2019 at 6:19
  • this is exactly what I want =IFERROR(query(D5:EG113, "select count(E) where E='UB' label count(E) ''"),"")
    – Radek
    Oct 9, 2019 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.