See inline...

He did say that Gary's formula "worked like a charm"!<bg

But he may not have tested it with the text in C1 repeated within a single
cell in which case it may have only **looked** like it worked the way he
wanted. And this seems to be the case as I just got a response from the OP
to one of my other messages indicating my reading of the question was
correct.

I only interjected to mention (belatedly) that Sumproduct() made arrays
unnecessary, and to also revise Gary's formula to *not* be case sensitive.

BTW ... to kick a dead horse <bg ... 1 less function call:

=SUMPRODUCT(--ISNUMBER(SEARCH(C1,A1:A5)))

Yes, I keep forgetting the --ISNUMBER in place of NOT(ISERROR) "trick"... I
know it, I just seem to have this mental block against using it for some
reason. Thanks for posting it.

Rick