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

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?

CodePudding user response：

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)

CodePudding user response：

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