Query continues to select rows despite 'where not null'

Question

I'm using a query command in google sheets to take the left variable of a text string, convert it to a number and sum it with other columns. When I attempt to use a query command of where Col1 is not null it continues to pull each row leaving a sheet full of zeros. What am I missing? Here's the full formula

=Query(ArrayFormula(value(left(T2:AC))),"select Col1+Col2+Col3+Col4+Col5+Col6+Col7+Col8+Col9+Col10,Col9 where Col1 is not null label Col1+Col2+Col3+Col4+Col5+Col6+Col7+Col8+Col9+Col10 'Total', Col9 'Q9'")

Answer 1

VALUE converts empty strings ("") to 0, as you can see on of the examples from the official Docs.

Because of this, before using VALUE to a cell, you should first check if the cell is empty. One option would be to use ISBLANK:

=ARRAYFORMULA(IF(ISBLANK(E2:F),,VALUE(LEFT(E2:F))))