CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
denlau
denlau

Reputation: 1016

How to get "0"-value instead of empty/blank in Google Sheets Query

I am currently trying to sum some numbers from another sheet in Google Sheets with the QUERY-function.

The data is from a CSV export, so I'd rather not change the data input (which is in another sheet).

I have the following query:

=QUERY(Orders!A:P;"SELECT A,C, P+C WHERE A > 1 label P+C 'Total'";1)

My problem is, that column P will either be empty or have a numeric value. The problem is, that the "P+C" will void when P is blank.

Therefore I want the value to only return the value of "C" in the columns when P is empty - and else return P+C.

Is that even possible?

Upvotes: 0

Views: 1513

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15318

Try

=QUERY({Orders!A:O\arrayformula(if(Orders!P:P="";0;Orders!P:P))};"SELECT Col1,Col3, Col16+Col3 WHERE Col1 > 1 Label Col16+Col3 'Total' ";1)

Upvotes: 2

player0
player0

Reputation: 1

How to get "0"-value instead of empty/blank in Google Sheets Query

multiply it by 1:

=INDEX(QUERY(Orders!A:P; "SELECT A,C,P+C WHERE A > 1 label P+C 'Total'"; 1)*1)

if you got errors from multiplication use:

=INDEX(IFERROR(QUERY(Orders!A:P; "SELECT A,C,P+C WHERE A > 1 label P+C 'Total'"; 1)*1; 
               QUERY(Orders!A:P; "SELECT A,C,P+C WHERE A > 1 label P+C 'Total'"; 1))

Upvotes: 1

Related Questions