COUNT values in one Column for Distinct Value in Another Column - EXCEL

Question

Hello All,

I have two columns in Excel Worksheet and want to count column 2 for distinct values in Column 1.

Table:

Column 1	Column 2
Apple	Yes
Orange	Yes
Apple	Yes
Banana	No
Grapes	No
Banana	No
Orange	Yes

Desired OUTPUT / RESULT:

Column 1	Column 2
YES	2
NO	2

Please help.

Thank You

Answer 1

Try below formula-

=SUMPRODUCT(($B$2:$B$8=E2)/COUNTIF($A$2:$A$8,$A$2:$A$8&""))

If you have Excel365 then use below formulas-

E2=UNIQUE(B2:B8)
F2=COUNTA(UNIQUE(FILTER($A$2:$A$8,$B$2:$B$8=E2)))