Help needed on a Combo COUNT Multiple Text Items with CONTAINS

[GTdiscus]

All,

Thank you in advance for your help. I am trying to do what I believe is a combo of a COUNT and a CONTAINS function. I would like to compare just one cell to another set of cells and count how many items within the cell being checked are contained in the other cells. An example will help make this more clear.

Cell to check (we shall call it 1) against the references has: A, B, C

Reference cell 2 has: A, D, E
Reference cell 3 has: A, B, C, D
Reference cell 4 has: X, Y, Z

The equation would check to see how many of 1's items (3 total in the example that are text) are in cells 2, 3 and 4. The correct response would be for each as follows:
Cell 2: 1 (A is present here...so it just has one matching...so just 1 as the count)
Cell 3: 3 (All of them from 1 are here...D doesn't matter, as it isn't one of the ones I'm looking for)
Cell 4: 0 (nothing from 1 is present)

Hope this makes sense!

Best wishes,
Scott

 

[DRSteele]

Is this getting closer?

The orange items in the test list are the ones that contain the items in the Reference list; and the dark orange one contains two of the items in the reference list.

	E	F	G	H	I	J	K	L	M	N
14	Skill 1	Skill 2	Skill 3	Skill 4	Skill 5	Skill 6	Skill 7	Skill 8	Skill 9	Skill 10
15	root cause analysis	pareto chart	regression	process mapping	simulation	Statistic	DESIGN OF EXPERIMENTS	Simulation DESIGN	Kitten	Kitten Walking Statistics
16
17	Reference List
18	Simulation
19	Design of Experiments
20	Statistics
21	Kitten Walking
22	Present:	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

is in list​

Array Formulas Cell Formula F22 {=SUM(--ISNUMBER(SEARCH(E18:E21,E15:N15)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>

 

[GTdiscus]

That did indeed do the trick!
THANK YOU (AGAIN)!
-Scott

 

[DRSteele]

Ah! Super. You're welcome.