x-no-archive: yes

I have a set of data comprised of user IDs separated by a space
(output from a logging application): A sample cell might contain the
following value (only a space between the values):

Test.Guy Sample.User Admin

I also have a range containing a subset of the possible values (user
names), each in their own set of cells, such as:
Test.Guy
Sample.user

I would like to craft a formula that counts the number of users from
the list that appear in the cells. So ideally, I would have a cell
that reports "Number of OUR users" as 2 for the above example, as two
of the three values are in my range of user IDs.

Does anyone have any suggestions on how to accomplish this?

Say A1 contains:
alpha beta gamma delta zeta eta theta
all in one cell and that B8 thru B13 contain:

beta
zeta
alpha
cat
dog
flower

In C8 enter:
=IF(LEN(A$1)=LEN(SUBSTITUTE($A$1,B8,"")),0,1) and copy down

We see in B8 thru C13:

beta 1
zeta 1
alpha 1
cat 0
dog 0
flower 0

so:
=SUM(C8:C13) will give the number of items in the list that appear un the
single cell.
--
Gary''s Student - gsnu200811


"hfx.selling" wrote:

> x-no-archive: yes
>
> I have a set of data comprised of user IDs separated by a space
> (output from a logging application): A sample cell might contain the
> following value (only a space between the values):
>
> Test.Guy Sample.User Admin
>
> I also have a range containing a subset of the possible values (user
> names), each in their own set of cells, such as:
> Test.Guy
> Sample.user
>
> I would like to craft a formula that counts the number of users from
> the list that appear in the cells. So ideally, I would have a cell
> that reports "Number of OUR users" as 2 for the above example, as two
> of the three values are in my range of user IDs.
>
> Does anyone have any suggestions on how to accomplish this?
>

One way...

A1 = Test.Guy Sample.User Admin

D12 = Test.Guy, Sample.user

=SUMPRODUCT(--(ISNUMBER(SEARCH(D12,A1))))


--
Biff
Microsoft Excel MVP


"hfx.selling" <(E-Mail Removed)> wrote in message
news:8d5d6def-512e-430c-b50e-(E-Mail Removed)...
>
> I have a set of data comprised of user IDs separated by a space
> (output from a logging application): A sample cell might contain the
> following value (only a space between the values):
>
> Test.Guy Sample.User Admin
>
> I also have a range containing a subset of the possible values (user
> names), each in their own set of cells, such as:
> Test.Guy
> Sample.user
>
> I would like to craft a formula that counts the number of users from
> the list that appear in the cells. So ideally, I would have a cell
> that reports "Number of OUR users" as 2 for the above example, as two
> of the three values are in my range of user IDs.
>
> Does anyone have any suggestions on how to accomplish this?

x-no-archive: yes

Awesome, that worked perfectly. I was going to do something similar to
the option Gary's student proposed, but this formula worked like a
charm. I should've mentioned that there's hundreds and hundreds of
those 'data' cells to check, so this option is certainly preferable.

Thanks so much - my Monday morning is saved! :-)

On Nov 1, 1:43*pm, "T. Valko" <biffinp...@comcast.net> wrote:
> One way...
>
> A1 = Test.Guy Sample.User Admin
>
> D12 = Test.Guy, Sample.user
>
> =SUMPRODUCT(--(ISNUMBER(SEARCH(D12,A1))))
>
> --
> Biff
> Microsoft Excel MVP
>
> "hfx.selling" <hfx.n...@gmail.com> wrote in message
>
> news:8d5d6def-512e-430c-b50e-(E-Mail Removed)...
>
>
>
> > I have a set of data comprised of user IDs separated by a space
> > (output from a logging application): A sample cell might contain the
> > following value (only a space between the values):
>
> > Test.Guy Sample.User Admin
>
> > I also have a range containing a subset of the possible values (user
> > names), each in their own set of cells, such as:
> > Test.Guy
> > Sample.user
>
> > I would like to craft a formula that counts the number of users from
> > the list that appear in the cells. So ideally, I would have a cell
> > that reports "Number of OUR users" as 2 for the above example, as two
> > of the three values are in my range of user IDs.
>
> > Does anyone have any suggestions on how to accomplish this?

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"hfx.selling" <(E-Mail Removed)> wrote in message
news:1492ecef-d7dd-4b75-90c0-(E-Mail Removed)...

Awesome, that worked perfectly. I was going to do something similar to
the option Gary's student proposed, but this formula worked like a
charm. I should've mentioned that there's hundreds and hundreds of
those 'data' cells to check, so this option is certainly preferable.

Thanks so much - my Monday morning is saved! :-)

On Nov 1, 1:43 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> One way...
>
> A1 = Test.Guy Sample.User Admin
>
> D12 = Test.Guy, Sample.user
>
> =SUMPRODUCT(--(ISNUMBER(SEARCH(D12,A1))))
>
> --
> Biff
> Microsoft Excel MVP
>
> "hfx.selling" <hfx.n...@gmail.com> wrote in message
>
> news:8d5d6def-512e-430c-b50e-(E-Mail Removed)...
>
>
>
> > I have a set of data comprised of user IDs separated by a space
> > (output from a logging application): A sample cell might contain the
> > following value (only a space between the values):
>
> > Test.Guy Sample.User Admin
>
> > I also have a range containing a subset of the possible values (user
> > names), each in their own set of cells, such as:
> > Test.Guy
> > Sample.user
>
> > I would like to craft a formula that counts the number of users from
> > the list that appear in the cells. So ideally, I would have a cell
> > that reports "Number of OUR users" as 2 for the above example, as two
> > of the three values are in my range of user IDs.
>
> > Does anyone have any suggestions on how to accomplish this?