compare multiple values in one cell to a range of values in multiplecells?

[hfx.selling]

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?

 

[Gary''s Student]

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.

 

[T. Valko]

One way...

A1 = Test.Guy Sample.User Admin

D12 = Test.Guy, Sample.user

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

 

[hfx.selling]

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!

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP



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!