Skip to main content
added 5 characters in body
Source Link
Erik Tyler
  • 5.1k
  • 2
  • 8
  • 11

You are used to an empty cell having a value of zero in other functions. However, QUERY acts uniquely from other functions. As currently set up, you are asking QUERY to subtract a null string from a number (i.e., to act on two different data types), and QUERY makes no assumptions about what those "should mean" (i.e., it is literal). This is the same reason QUERY only returns one data type (the most populous one) per column if there are mixed data types in that column.

One approach would be to pre-process the range and then use Colx notation:

=ArrayFormula(QUERY({A2:A,IF(B2:C="",0,B2:C)},"select sum(Col2) - sum(Col3) where Col1 = 'ard'",0))

However, you'd be better of just using a function that does make the assumption that null means zero:

=FILTER=SUM(FILTER(B2:B-C2:C,A2:A="ard"))

You are used to an empty cell having a value of zero in other functions. However, QUERY acts uniquely from other functions. As currently set up, you are asking QUERY to subtract a null string from a number (i.e., to act on two different data types), and QUERY makes no assumptions about what those "should mean" (i.e., it is literal). This is the same reason QUERY only returns one data type (the most populous one) per column if there are mixed data types in that column.

One approach would be to pre-process the range and then use Colx notation:

=ArrayFormula(QUERY({A2:A,IF(B2:C="",0,B2:C)},"select sum(Col2) - sum(Col3) where Col1 = 'ard'",0))

However, you'd be better of just using a function that does make the assumption that null means zero:

=FILTER(B2:B-C2:C,A2:A="ard")

You are used to an empty cell having a value of zero in other functions. However, QUERY acts uniquely from other functions. As currently set up, you are asking QUERY to subtract a null string from a number (i.e., to act on two different data types), and QUERY makes no assumptions about what those "should mean" (i.e., it is literal). This is the same reason QUERY only returns one data type (the most populous one) per column if there are mixed data types in that column.

One approach would be to pre-process the range and then use Colx notation:

=ArrayFormula(QUERY({A2:A,IF(B2:C="",0,B2:C)},"select sum(Col2) - sum(Col3) where Col1 = 'ard'",0))

However, you'd be better of just using a function that does make the assumption that null means zero:

=SUM(FILTER(B2:B-C2:C,A2:A="ard"))

Source Link
Erik Tyler
  • 5.1k
  • 2
  • 8
  • 11

You are used to an empty cell having a value of zero in other functions. However, QUERY acts uniquely from other functions. As currently set up, you are asking QUERY to subtract a null string from a number (i.e., to act on two different data types), and QUERY makes no assumptions about what those "should mean" (i.e., it is literal). This is the same reason QUERY only returns one data type (the most populous one) per column if there are mixed data types in that column.

One approach would be to pre-process the range and then use Colx notation:

=ArrayFormula(QUERY({A2:A,IF(B2:C="",0,B2:C)},"select sum(Col2) - sum(Col3) where Col1 = 'ard'",0))

However, you'd be better of just using a function that does make the assumption that null means zero:

=FILTER(B2:B-C2:C,A2:A="ard")