CODEWITHSUNDEEP
google-sheets
user13876647
user13876647

Reputation:

Google Sheet Query Formula Issue

Using below Query formula it is working perfectly but there is 1 issue in it, that if first Column is empty such as "AB" it will not work (Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC) that error is occur.

I have multiple sheets where mostly multiple columns are empty i mean If AB is empty then AC has values then AD is empty and so on.

please help in this regard.

=QUERY(A3:AZ27,"Select A, SUM(AB), SUM(AC), SUM(AD), SUM(AE), SUM(AF), SUM(AG), SUM(AH) Where A is not null Group by A LABEL A 'SuitA', SUM(AB) 'Jan', SUM(AC) 'FEB', SUM(AD) 'MAR', SUM(AE) 'APR', SUM(AF) 'MAY', SUM(AG) 'JUN', SUM(AH) 'JUL' ",1)

Upvotes: 0

Views: 334

Answers (1)

zummon
zummon

Reputation: 986

It seems like =QUERY() doesn't have enough data to read

Your code I tried using is technically correct, but you need to fill more data

so use QUERY with ARRAY will work

=QUERY({A3:AA27,ARRAYFORMULA(AB3:AH27+0),AI3:AZ27}, "
  Select Col1, SUM(Col28), SUM(Col29), SUM(Col30), SUM(Col31), SUM(Col32), SUM(Col33), SUM(Col34) 
  Where Col1 is not null 
  Group by Col1 
  LABEL Col1 'SuitA', SUM(Col28) 'Jan', SUM(Col29) 'FEB', SUM(Col30) 'MAR', SUM(Col31) 'APR', SUM(Col32) 'MAY', SUM(Col33) 'JUN', SUM(Col34) 'JUL'
", 1)

add {range} to change range reference to array

then QUERY needs to change from "SELECT A, AB,..." to "SELECT Col1, Col28,..." if you provide continuously as query({A3:AA27},...) But if you provide just query({B3:AA27},...) then column B = Col1 and go on until last column AA = Col27

ARRAYFORMULA(range+0) to force empty cells to be filled with zero

QUERY FOR TOO SMALL DATA

Upvotes: 2

Related Questions