Help counting unique values with another criteria

[sigma87]

I am trying to count the unique number of events that occur in a certain location.

Unique Value Count Help.xlsx

What is the best formula to use?

Thanks!!

[Fotis1991]

Try in K2 and copy down this ARRAY formula.

=COUNT(1/FREQUENCY(IF($B$2:$B$20=J2,MATCH(J2&$A$2:$A$20,$B$2:$B$20&$A$2:$A$20,0)),ROW($A$2:$A$20)-1))



Edit: If you have too many(thousands...?) of rows, my suggestion is to use a helper column and then a regular formula..This will be much more faster..

[cpmsimoes]

Hello sigma87

Try this:
For Mile1 =SUM(IF(FREQUENCY(MATCH(A2:A6,A2:A6,0),MATCH(A2:A6,A2:A6,0))>0,1))
For Mile2 =SUM(IF(FREQUENCY(MATCH(A7:A12,A7:A12,0),MATCH(A7:A12,A7:A12,0))>0,1))
For Mile3 =SUM(IF(FREQUENCY(MATCH(A13:A20,A13:A20,0),MATCH(A13:A20,A13:A20,0))>0,1))

Have a nice day

edited: The solution proposed by Fotis1991 is better.