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 you have read our privacy policy.

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