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