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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>