2

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

enter image description here

Iamblichus
  • 18,540
  • 2
  • 11
  • 27
jhaff
  • 25
  • 4
  • Where is the `is not null` clause in your formula? https://developers.google.com/chart/interactive/docs/querylanguage#where – Muhammad Hamza Aug 26 '20 at 15:02
  • Updated. I had removed it from my sheet since it was causing issues and forgot to add it back in before copying to here. – jhaff Aug 26 '20 at 15:54
  • Add a sample data of `T2:T` and the result of the first column of `ArrayFormula(value(left(T2:AC)))` – Rubén Aug 27 '20 at 00:34
  • Updated with data and requested formula (couldn't embed the image). Looks like the `value` function is converting every empty cell into a 0. So then when the array gets passed to the `query` command, it is not null. – jhaff Aug 27 '20 at 03:37
  • Hi, I posted an answer regarding this. Could you please confirm whether that solves your issue? – Iamblichus Aug 27 '20 at 11:30

1 Answers1

0

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

enter image description here

Iamblichus
  • 18,540
  • 2
  • 11
  • 27