Counting unique items in a list based on a condition
 

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
D19:D224. 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

Counting unique items in a list based on a condition
 

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

"George Lynch" wrote:

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
D19:D224. 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

Counting unique items in a list based on a condition
 

Have you tried a Pivot Table? It can give you a count of all unique names
organized by title.
--
Gary's Student


"George Lynch" wrote:

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
D19:D224. 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

Counting unique items in a list based on a condition
 

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.

"Gary's Student" wrote:

Have you tried a Pivot Table? It can give you a count of all unique names
organized by title.
--
Gary's Student


"George Lynch" wrote:

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
D19:D224. 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

Counting unique items in a list based on a condition
 

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 D19:D224. 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

"Alok" wrote:

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

"George Lynch" wrote:

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
D19:D224. 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