Counting Unique Values with an IF statement as well

[Fursmanm]

Hi,

Basically, i want to count the number of managers registered to each location, but the problem is some managers get registered several times at different dates (but i only want to count once for each unique name).


So for example we can have

location manager name
Location 1 Mr Yellow
Location 1 Mr Yellow
Location 1 Mr Black
Location 2 Mr White
Location 2 Mr White
Location 2 Mr Red
Location 2 Mr Green
Location 2 Mr Green
Location 3 Mr Purple
Location 3 Mr Red
Location 3 Mr Green
Location 4 Mr Blue

So for this data set the counts i would want are:

Location 1: 2
Location 2: 3
Location 3: 3
Location 4: 1

Preferably if i could have this in a column on the right of the original data that would work,

I cant do a simple count unique of column 2, because it needs to be a unique count for each location name.

Anyone got any ideas?

Regards,

[benishiryo]

hi Fursmanm, try this array formula:
=SUM(SIGN(FREQUENCY(IF($A$2:$A$13=D1,MATCH($B$2:$B$13,$B$2:$B$13,0)),ROW($B$2:$B$13)-ROW($B$2)+1)))

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.