Excel formula with multiple criteria

[palwirox]

[TABLE="width: 680"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Recruiter[/TD]
[TD]Nurse[/TD]
[TD]Hospital[/TD]
[/TR]
[TR]
[TD]Jack min[/TD]
[TD]Joe Case[/TD]
[TD]Palomar Medical Center[/TD]
[/TR]
[TR]
[TD]Jack min[/TD]
[TD]Amanda Duggan[/TD]
[TD]Community Hospital Long Beach[/TD]
[/TR]
[TR]
[TD]Jack min[/TD]
[TD]Jose Case[/TD]
[TD]Kern Medical Center[/TD]
[/TR]
[TR]
[TD]Asngela Rosa[/TD]
[TD]Burglary Singh[/TD]
[TD]Lodi Memorial Hospital[/TD]
[/TR]
[TR]
[TD]Asngela Rosa[/TD]
[TD]Rose Sharh[/TD]
[TD]Twin Cities [/TD]
[/TR]
[TR]
[TD]Asngela Rosa[/TD]
[TD]Rose Sharh[/TD]
[TD]Lodi Memorial Hospital[/TD]
[/TR]
[TR]
[TD]Asngela Rosa[/TD]
[TD]Rose Sharh[/TD]
[TD]Twin Cities [/TD]
[/TR]
[TR]
[TD]Asngela Rosa[/TD]
[TD]Burglary Singh[/TD]
[TD]Twin Cities [/TD]
[/TR]
[TR]
[TD]Rose Shah[/TD]
[TD]Christina Bolt[/TD]
[TD]USC Verdugo Hills[/TD]
[/TR]
[TR]
[TD]Rose Shah[/TD]
[TD]Christina Bolt[/TD]
[TD]Community Hospital Long Beach[/TD]
[/TR]
[TR]
[TD]Rose Shah[/TD]
[TD]Christina Bolt[/TD]
[TD]Twin Cities [/TD]
[/TR]
[TR]
[TD]Rose Shah[/TD]
[TD]Ping Shah[/TD]
[TD]Rady Children's Hospital [/TD]
[/TR]
[TR]
[TD]Rose Shah[/TD]
[TD]Pal Singh[/TD]
[TD]JFK Indio[/TD]
[/TR]
</tbody>[/TABLE]

Question:
I wanted to count unique hospitals for each recruiter. How should i create count ifs formula??

Please help.

 

[T. Valko]

Create a list of the unique recruiters. Let's assume that list is in the range G2:G4.

Enter this array formula** in H2 and copy down:

=SUM(IF(FREQUENCY(IF(A$2:A$14=G2,MATCH(C$2:C$13,C$2:C$13,0)),ROW(C$2:C$13)-ROW(C$2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.