Count the occurance of value

[vjs2445]

I have two sheets:

Sheet 1
Col A
A
B
C
D

Sheet 2
Col A Col B
A XX
B YY
AXX
A Z
D D
DD

I want to count the number of unique occurance of value in Col B in Sheet 2 as per value in Col A in sheet 1 and Sheet2.


My output result should be like following:


Result
Col A Count of col B value in Sheet 2
A 2
B 1
C 0
D 1






Thanks for help.

[daffodil11]

B1:

=SUM(IF(FREQUENCY(IF(Sheet2!$A$1:$A$6=A1,IF(Sheet2!$B$1:$B$6<>"",MATCH(Sheet2!$B$1:$B$6,Sheet2!$B$1:$B$6,0))),ROW($B$1:$B$6)-ROW(A2)+1),1))

Confirmed with Ctrl+Shift+Enter

and copy down

[vjs2445]

daffodil11, Thanks for your help.

Regards,

[daffodil11]

No problem.