google sheets image

I need to obtain the sum(B) if the column has empty values I want to reflect 0. I am automating this process for multiple sheets using app-script and need to use QUERY. Any suggestions?


2 Answers 2


Use n() to convert blanks to zeros, like this:

=arrayformula( query( n(A2:B), "select sum(Col1), sum(Col2)", 0 ) )


If you want to use QUERY, how about this formula in cell D1:

={query(A2:A,"select sum(A) label sum(A) ''"),if(query(B2:B,"select count(B) label count(B) ''")=0,0,query(B2:B,"select sum(B) label sum(B) ''"))}

An alternative would be:



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.