CODEWITHSUNDEEP
google-sheetsformulas
vatavale
vatavale

Reputation: 1620

Google Sheets: sum two columns by QUERY with some empty cells

I have try to get sum of two columns using query function in Google Sheets.

  Col1   Col2    Col3
  -----------------------
  12             User1
  23     44      creature
         55      User1
  14             User1

This work fine if there are at least one number in each column:

=QUERY(IMPORTRANGE('SomeURL';"Page!A1:C");
"select (sum(Col1) + sum(Col2)) where Col3 = 'User1'")

However this query cause error QUERY:AVG_SUM_ONLY_NUMERIC if all cells in one column are empty in result set.

  Col1   Col2    Col3
  -----------------------
  12             User1
  23     44      creature
                 User1
  14             User1

How can I get sum of columns using query function, if sometimes the cells are empty in one of the column?

Upvotes: 0

Views: 4393

Answers (2)

Karl_S
Karl_S

Reputation: 3554

You can SUM the Query, like this: =sum(query('SomeURL';"Page!A1:C"); "select Col1, Col2 where Col3 ='User1'"))

OR use SUMIF() twice, once for each column. It means 2 importranges, though, so it will probably be slower.

Upvotes: 2

TheMaster
TheMaster

Reputation: 50462

    =ARRAYFORMULA(SUM(query(IMPORTRANGE("url","page!A1:C6"),"select Col1,Col2 where Col3 = 'User1'")))

This should work for a simple sum. But i don't think there's a way inside QUERY to consider blanks as zero or assume them as numbers. If you could actually import range into sheet(i.e., use them as helper columns), then you can use ARRAYFORMULA(Query ({filter (A1:B6*1,NOT(ISEMAIL(A1:A6))),C1:C6}, "select *.... You should convert blanks outside Query (by *1)or Sum them outside query. Or use a DOUBLE Query and double import range, which would be performance depreciative.

Upvotes: 1

Related Questions