=SUM(IF(FREQUENCY(IF((A1:A10<>"")*(B1:B10="P1"),MATCH(A1:A10,A1:A10,0)),ROW(
INDIRECT("1:"&ROWS(A1:A10))))>0,1))
as an array formula, commit with Ctrl-Shift-Enter
--
HTH
Bob Phillips
"Michael" <[email protected]> wrote in message
news:[email protected]...
> A B
> china P1
> korea P2
> japan P3
> china P2
> korea P2
> japan P1
> U.S P2
> India P1
> China P1
> U.S P2
>
> I'm working with about 8000 data, I try to used this formula
> =SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))>0,1)) to
> count the unique entries in column A, which result 5. (China,
> japan,korea,india,U.S)
>
> However, i want to count unique entries in, let say P1 only, which should
be
> 3. How to use function to get this result?
>
Bookmarks