How can I avoid having to put 0s into the NULL fields to get a correct query calculation in Google Sheets

0
votes

I have a Google Sheets question, which I have not been able to figure out yet with Google-Fu and RTFM:

Take the following spreadsheet as an example: https://docs.google.com/spreadsheets/d/1IvMVaUdUDfYOoKyG0Uwd2n0M1mLjOTE5yZQ9K2R3q2M/edit?usp=sharing

In case the sheet gets lost in time, I am going to post its contents here:

Sheet1:

foo withdrawal deposit
C 4 10
D 10
E 10 4

As you see here, the withdrawal field for the D value being foo is empty, i.e. null

Sheet2:

foo balance
C =INDEX(QUERY({Sheet1!$A$2:C}, "SELECT SUM(Col3) - SUM(Col2) WHERE Col1 = '"&A2&"'"), 2)
D =INDEX(QUERY({Sheet1!$A$2:C}, "SELECT SUM(Col3) - SUM(Col2) WHERE Col1 = '"&A3&"'"), 2)
E =INDEX(QUERY({Sheet1!$A$2:C}, "SELECT SUM(Col3) - SUM(Col2) WHERE Col1 = '"&A4&"'"), 2)

The result is

foo balance
C 6
D
E -6

As you see, the balance field for the category D is null, although it should be -10.

The fix for that is to put a 0 into the deposit field in Sheet1 explicitly.

In my example, I get that data using a csv-export, and fields are generally empty and not 0, and it is cumbersome to add the 0 there. Is there a way to have something like COALESCE in that sum there (like in SQL)?

Please let me know.

2
google-sheetsgoogle-sheets-formula

2 Answers

2
votes

it seems like something quite a bit simpler would avoid the problem:

=SUMPRODUCT(Sheet1!C:C-Sheet1!B:B,Sheet1!A:A=A2)

for cell B2.

1
votes

Why don't you just add this in cell A1 of Sheet2 instead of all the Query:

=arrayformula({Sheet1!A1,"balance";if(Sheet1!A2:A<>"",{Sheet1!A2:A,Sheet1!C2:C-Sheet1!B2:B},)})

Obviously ensure cells Sheet2!A2:A and Sheet2!B1:B are empty.

If you have duplicate values of foo, try:

=arrayformula(query({Sheet1!A1,"balance";if(Sheet1!A2:A<>"",{Sheet1!A2:A,Sheet1!C2:C-Sheet1!B2:B},)},"select Col1,sum(Col2) where Col1 is not null group by Col1 label sum(Col2) 'balance'",1))

A better option for a single-cell formula, referencing multiple sheets would be:

=arrayformula(query(
{Sheet1!A:A,n(Sheet1!B:C);Sheet2!A2:A,n(Sheet2!B2:C);Sheet3!A2:A,n(Sheet3!B2:C)},
"select Col1,sum(Col3)-sum(Col2) where Col1 is not null group by Col1 label sum(Col3)-sum(Col2) 'balance' ",1))