Originally Posted by
XOR LX
Sure. For the formula in C3, for example, we simply count how many times each of the entries in the range A1:A14 occurs within the entry in B3.
We might do this by a simpler formula than that which I posted, viz:
=SUMPRODUCT(0+ISNUMBER(FIND(A$1:A$14,B1)))
and indeed, given the data you provided, this would also give correct results.
However, I assumed that you were using dummy data and that, in reality, your entries were not as simple as single letters of the alphabet. As such, it is perferable to employ a more rigorous version of the formula, as I gave. The reason being that, if, for example, B3 contained, not "D,E,F", but "DA, E, F", then the formula:
=SUMPRODUCT(0+ISNUMBER(FIND(A$1:A$14,B3)))
would incorrectly return 11, since the "A"s in column A are being considered as being part of this entry.
By first adding a comma before and after each of the entries in A1:A14 - and also to that in B3 - we ensure that the correct result is returned, since, now, ",A," is not found within ",DA,E,F,".
Regards
Bookmarks