Counting Unique Names based on Criteria

**adamsiskin** · 08-15-2012, 03:51 PM

I have attached a document that I need a formula for. In column B16, I need a formula that will count the number of unique people who are in radiology. As you can see, Adam, Ben, and Susan have radiology next to their name. I want the count to come out to 3 versus 7, since only 3 people have radiology in their name versus the amount of times their name come up.

**NBVC** · 08-15-2012, 03:59 PM

Try:

=COUNT(1/FREQUENCY(IF($B$2:$B$12=A16,IF($A$2:$A$12<>"",MATCH($A$2:$A$12,$A$2:$A$12,0))),ROW($A$2:$A$12)-ROW($A$2)+1))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.

Note: the Adam in A4 has an extra space in it.. remove it for more accurate result.

**adamsiskin** · 08-15-2012, 04:02 PM

That did not work

---------- Post added at 03:02 PM ---------- Previous post was at 03:02 PM ----------

oh wait it did

**Ron Coderre** · 08-15-2012, 04:10 PM

Try this regular formula...(after removing the extra space after the name in A4 "adam ")

Please Login or Register  to view this content.

Copy that formula into B17

Is that something you can work with?

**adamsiskin** · 08-15-2012, 04:26 PM

The actual worksheet I am using is coming from different worksheets. I noticed that your formula has blank cells in it "_". Is there a way we can make the formula simplified like the newer one provided without referencing the blank cells?

---------- Post added at 03:26 PM ---------- Previous post was at 03:24 PM ----------

If I had to do the calculation on another page, I need the formula to work

**NBVC** · 08-15-2012, 04:27 PM

Which of us are you talking too specifically? and what blank cells do you mean?

**adamsiskin** · 08-15-2012, 04:49 PM

I was referring to Ron. NBVC your formula works when I apply it to one person, however, when I tried to copy it to 50 ppl excel bugged out and froze on me since I have a lot of data.

**NBVC** · 08-15-2012, 04:55 PM

I am not sure if Ron's would be much more efficient, as it really is an array formula in disguise....

Perhaps using a helper column...

e.g. in C2:

=B2&"_"&(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1)

copied down

then B16:

=COUNTIF($C$2:$C$12,A16&"_TRUE")

copied down