1

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.

1

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.