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?