Unique text count with multiple criteria?

[Dan-X !]

This formula is working but what it's doing right now is giving me a
total unique count of names PER QUEUE. Sometimes agents work in two or
more queues per day, so what I need it to do is give me a total unique
number of names even if the same name shows up under multiple queues.
Make sense? If so, does someone know how to make the formula do that?

=SUMPRODUCT(((Queue_User_Routing.csv!$C$2:$C$2000="Appeal")+(Queue_User_Routing.csv!$C$2:$C$2000="Duplicate
Resolution")+(Queue_User_Routing.csv!$C$2:$C$2000="Manual
Determination")+(Queue_User_Routing.csv!$C$2:$C$2000="Recertification")+(Queue_User_Routing.csv!$C$2:$C$2000="Supervisor
Review")+(Queue_User_Routing.csv!$C$2:$C$2000="NPSC Program
Review")*(Queue_User_Routing.csv!$B$2:$B$2000<>"")/COUNTIF(Queue_User_Routing.csv!$B$2:$B$2000,Queue_User_Routing.csv!$B$2:$B$2000&"")))

 

[RagDyer]

Try this:

=SUMPRODUCT((Queue_User_Routing.csv!$B$2:$B$2000<>"")/COUNTIF(Queue_User_Routing.csv!$B$2:B$2000,Queue_User_Routing.csv!$B$2:$B$2000&""))

Will give you count of unique names in Column B, no matter what queue.

 

[Dan-X !]

Thanks for the response. Your formula does work but I need to get a
count just for certain queues. At work they group multiple queues
together based on what type of work has to be done to the cases in them
so they want a count of the agents working on "Group I Queues", "Group
II Queues", etc. So I need a count that will give me a unique number
of agents working in specific multiple queues.

 

[Dan-X !]

Also, this formula works:

=SUMPRODUCT(([Queue_User_Routing.csv]Queue_User_Routing!$C$2:$C$2000={"Duplicate
Investigation","Manual Flood Plain Mapping","Post Inspection Flood
Plain
Mapping"})*([Queue_User_Routing.csv]Queue_User_Routing!$B$2:$B$2000<>""))

However, if an agent worked in more than one of the queues it counts
each queue worked. As the formula above is written it returns a number
of 42. There were four agents that worked two of the queues, so they
are getting counted twice. Therefore, the correct number returned
needs to be 38. Am I explaining this well enough?

Thanks for your help!
-Danny

 

[RagDyer]

I left out your paths to make it easier for me to test, but this works for
me:

=SUMPRODUCT(($C$2:$C$2000={"Appeal","Duplicate Resolution","Manual
Determination","Recertification","Supervisor Review","NPSC Program
Review"})*($B$2:$B$2000<>"")/COUNTIF($B$2:B$2000,$B$2:$B$2000&""))

Paste it into a cell in your "Queue_User_Routing" sheet so that you can test
it without possibly making a mistake when you type in your path.

If it works there, then copy it to your main WB.