Counting Unique text entries in a sheet with a condition
 

I have a list of names with mutiple entries for most of them
I need to count the unique names but that only meet another criteria

eg
A B
Uk Mike
DE Fred
Uk Mike
Ch Ted
Uk Rose

What I need is a formulae that will let me specify if colulm A is UK count
the unique names in colum B - the answer should be 2 (Mike & Rose are in UK)

Thanks

Hi Mike,

Try this

=SUMPRODUCT((A1:A100="UK")/COUNTIF(B1:B100,B1:B100&"")*(B1:B100<""))

--
HTH

Bob Phillips

"Mike" wrote in message
...
I have a list of names with mutiple entries for most of them
I need to count the unique names but that only meet another criteria

eg
A B
Uk Mike
DE Fred
Uk Mike
Ch Ted
Uk Rose

What I need is a formulae that will let me specify if colulm A is UK count
the unique names in colum B - the answer should be 2 (Mike & Rose are in
UK)

Thanks

Bob
Thank you so much - your a top man - did the trick I was messing with
FREQUENCY & MATCH functions which were driving me crazy.

Can I add more than one Criteria eg UK and maybe another coloum criteria?

Thanks
Mike

"Bob Phillips" wrote:

Hi Mike,

Try this

=SUMPRODUCT((A1:A100="UK")/COUNTIF(B1:B100,B1:B100&"")*(B1:B100<""))

--
HTH

Bob Phillips

"Mike" wrote in message
...
I have a list of names with mutiple entries for most of them
I need to count the unique names but that only meet another criteria

eg
A B
Uk Mike
DE Fred
Uk Mike
Ch Ted
Uk Rose

What I need is a formulae that will let me specify if colulm A is UK count
the unique names in colum B - the answer should be 2 (Mike & Rose are in
UK)

Thanks

It can be done with Frequency and Match

=COUNT(1/FREQUENCY(IF((A1:A100="UK")*(B1:B100<""),MATCH(B1 :B100,B1:B100,0))
,ROW(INDEX(B1:B100,0,0))-ROW(B1)+1))

which as an array formula is committed with Ctrl-Shift-Enter, but the
SUMPRODUCT is easier.

For more conditions, you just add it to the first part, like so

=SUMPRODUCT((A1:A100="UK")*(C1:C100="other
value")/COUNTIF(B1:B100,B1:B100&"")*(B1:B100<""))

Regards

Bob

"Mike" wrote in message
...
Bob
Thank you so much - your a top man - did the trick I was messing with
FREQUENCY & MATCH functions which were driving me crazy.

Can I add more than one Criteria eg UK and maybe another coloum criteria?

Thanks
Mike

"Bob Phillips" wrote:

Hi Mike,

Try this

=SUMPRODUCT((A1:A100="UK")/COUNTIF(B1:B100,B1:B100&"")*(B1:B100<""))

--
HTH

Bob Phillips

"Mike" wrote in message
...
I have a list of names with mutiple entries for most of them
I need to count the unique names but that only meet another criteria

eg
A B
Uk Mike
DE Fred
Uk Mike
Ch Ted
Uk Rose

What I need is a formulae that will let me specify if colulm A is UK
count
the unique names in colum B - the answer should be 2 (Mike & Rose are
in
UK)

Thanks

Thank you Bob Very helpful
Mike

"Bob Phillips" wrote:

It can be done with Frequency and Match

=COUNT(1/FREQUENCY(IF((A1:A100="UK")*(B1:B100<""),MATCH(B1 :B100,B1:B100,0))
,ROW(INDEX(B1:B100,0,0))-ROW(B1)+1))

which as an array formula is committed with Ctrl-Shift-Enter, but the
SUMPRODUCT is easier.

For more conditions, you just add it to the first part, like so

=SUMPRODUCT((A1:A100="UK")*(C1:C100="other
value")/COUNTIF(B1:B100,B1:B100&"")*(B1:B100<""))

Regards

Bob

"Mike" wrote in message
...
Bob
Thank you so much - your a top man - did the trick I was messing with
FREQUENCY & MATCH functions which were driving me crazy.

Can I add more than one Criteria eg UK and maybe another coloum criteria?

Thanks
Mike

"Bob Phillips" wrote:

Hi Mike,

Try this

=SUMPRODUCT((A1:A100="UK")/COUNTIF(B1:B100,B1:B100&"")*(B1:B100<""))

--
HTH

Bob Phillips

"Mike" wrote in message
...
I have a list of names with mutiple entries for most of them
I need to count the unique names but that only meet another criteria

eg
A B
Uk Mike
DE Fred
Uk Mike
Ch Ted
Uk Rose

What I need is a formulae that will let me specify if colulm A is UK
count
the unique names in colum B - the answer should be 2 (Mike & Rose are
in
UK)

Thanks

Bob
Can I ask your help again pls
I now need to just count the numner of unique text enteries in a coloulm of
data
I have trie dto figur eit out but I must me being dumb?
can you advise pls

Mike

"Bob Phillips" wrote:

It can be done with Frequency and Match

=COUNT(1/FREQUENCY(IF((A1:A100="UK")*(B1:B100<""),MATCH(B1 :B100,B1:B100,0))
,ROW(INDEX(B1:B100,0,0))-ROW(B1)+1))

which as an array formula is committed with Ctrl-Shift-Enter, but the
SUMPRODUCT is easier.

For more conditions, you just add it to the first part, like so

=SUMPRODUCT((A1:A100="UK")*(C1:C100="other
value")/COUNTIF(B1:B100,B1:B100&"")*(B1:B100<""))

Regards

Bob

"Mike" wrote in message
...
Bob
Thank you so much - your a top man - did the trick I was messing with
FREQUENCY & MATCH functions which were driving me crazy.

Can I add more than one Criteria eg UK and maybe another coloum criteria?

Thanks
Mike

"Bob Phillips" wrote:

Hi Mike,

Try this

=SUMPRODUCT((A1:A100="UK")/COUNTIF(B1:B100,B1:B100&"")*(B1:B100<""))

--
HTH

Bob Phillips

"Mike" wrote in message
...
I have a list of names with mutiple entries for most of them
I need to count the unique names but that only meet another criteria

eg
A B
Uk Mike
DE Fred
Uk Mike
Ch Ted
Uk Rose

What I need is a formulae that will let me specify if colulm A is UK
count
the unique names in colum B - the answer should be 2 (Mike & Rose are
in
UK)

Thanks