Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

query against concatenated array in Google Sheets

I am trying to run а query against several ranges combined with {} like query({A2:C5, if(C1:C5='something',1,0)}, "select ..."). But I am getting an #REF! error with a message Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 4. Actual: 1. What is the reason for that?

Here is a detailed example. Suppose I have a table like that:

id  kind    color
1   a   green
2   a   green
3   b   green
4   c   blue

I want to get a table showing number of cells with green for each kind:

kind    color_count
a   2
b   1
c   0

Initially, I tried a query with the where clause for that:

=query(A2:C5, "select B, count(C) where C='green' group by B", -1)

But that does not include the row with zero values. So I tried to add an extra column with values 1 for the green color and 0 otherwise and use SUM over that without the where clause:

=query({A2:C5, if(C2:C5="green", 1, 0)}, "select B, sum(D) group by B", -1)

but that gives the above $REF!

As a workaround I added a column D to the table with the formula

=arrayformula(if(C2:C5="green", 1, 0))

Then the following query works and gives the desired result:

=query(A2:D5, "select B, sum(D) group by B", -1)

But is it possible to avoid this artificial column?

Solution

  • IF returns only 1 cell unless you use ARRAYFORMULA. so the error is on a spot because you have 4 cells on one side and 1 cell on the other side.

    try:

    =ARRAYFORMULA(QUERY({B:B, IF(C:C="green", 1, 0)}, 
 "select Col1,sum(Col2) 
  where Col1 is not null 
  group by Col1 
  label sum(Col2)''", 0))

    0