Counting unique items in a list based on a condition

[Guest]

I need to count unique names in a list, but only when the unique name meets a
condition (a specific title, for example) in another column. So let's say I
have my list of names (many repeats) in B19:B224, and my list of titles in
D19224. What I need to do is to count the unique names in the A column
where the title is "Director", or "Vice President", etc.

I can count the unique names in the A column with this formula:

=SUM(IF(FREQUENCY(MATCH($B$19:$B$224,$B$19:$B$224,0),MATCH($B$19:$B$224,$B$19:$B$224,0))>0,1))

I have thus far been unable to figure out a formula that will allow me to
count the unique names in the B column based on specific titles in the D
column.

Thanks in advance for any and all help...

George

 

[Guest]

Hi George,
Perhaps this formula will do the trick..
Here the A19:A225 are the titles within which you are looking for the unique
values

=SUMPRODUCT(--(A19:A225="T1"),--((FREQUENCY(MATCH($A$19:$A$224 &
B$19:$B$224,$A$19:$A$224 & $B$19:$B$224,0),MATCH($A$19:$A$224 &
$B$19:$B$224,$A$19:$A$224 & $B$19:$B$224,0))>0)))

Alok

 

[Guest]

Have you tried a Pivot Table? It can give you a count of all unique names
organized by title.

 

[Guest]

Hi Gary's Student,

I should have mentioned in my previous post that we have already tried pivot
tables and they do work, but this drill is part of a larger model, and the
pivot tables are too unwieldy for use in this project. That's why we're
trying to construct the formula. If the formula returns an accurate result,
that result will be picked up elsewhere in the model and used for other
calculations.

 

[Guest]

Hi Alok,

I'm a little confused by your formula; perhaps you can help me out. First,
I assume the T1 reference in the formula is where you entered the actual
title. I am curious why you enclosed that reference in quotes.

Also, I adjusted the cell references to the actual cells in my model. The
names are in B19:B224 and the titles are in D19224. But when I enter the
formula, whether normally or as an array, I get the #VALUE error.

I also don't understand the two dashes — or minus signs — after the
SUMPRODUCT function and in front of the FREQUENCY function.

Finally, I'm also not sure how the SUMPRODUCT works in this case. That is,
I know how to use the SUMPRODUCT to get a result based on conditions, but I
don't know how it's working here.

I really appreciate your help with this and I look forward to your response.

Thanks...

George