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.