Google-sheets – Using SUMPRODUCT with ARRAYFORMULA

google sheetsgoogle-sheets-arrayformulagoogle-sheets-query

I have the following random data in sheet one

A  orange
D  yellow
C  yellow
B  orange
B  orange
C  blue
A  yellow
D  blue
C  orange
C  blue

In sheet two I use the following query function:

A1=UNIQUE(QUERY(Sheet1!A1:B10,"select A, B order by A asc"))
C1=sumproduct(Sheet1!A:A=A1,Sheet1!B:B=B1)
C2=sumproduct(Sheet1!A:A=A2,Sheet1!B:B=B2)
C3=sumproduct(Sheet1!A:A=A3,Sheet1!B:B=B3)
.
.
.

Which returns the following:

A   orange 1
A   yellow 1
B   orange 2
C   yellow 1
C   blue   2
C   orange 1
D   yellow 1
D   blue   1

Instead of using sum product in each row, I want arrayformula to expand automatically.

When I use E1=ArrayFormula(sumproduct(A1:A10=C1:C,B1:B10=D1:D)) I get

Error: Array arguments to EQ are of different size.

Please help me out.

Best Answer

An array solution could be:

=ArrayFormula(IF(LEN(A:A),COUNTIF(Sheet1!A:A&CHAR(9)&Sheet1!B:B,A:A&CHAR(9)&B:B),))

although it might be better to generate the unique counts in the QUERY itself:

=QUERY(Sheet1!A1:C10,"select A, B, count(C) where A != '' group by A, B order by A asc label count(C) ''",0)

This relies on there being a column C present in Sheet1 (it doesn't matter what is actually in column C). However, if wasn't viable having a column C, then it could be worked around.

Related Topic