Short Version:

how do I do:
=SUMPRODUCT((B$2!$E$8:$E$100) * (B$2!$F$8:$F$100 = "*"&$A2&"*"))

Long Version:
How do I count the occurances or a sub-string in a range of cells of
strings, with a weighting.

COUNTIF does the occurances but, and SUMPRODUCT does the weighting, but how
do I combine them?

eg.

a|b|c, 4
a|c, 2
c, 4

gives me:

6 a's
10 c's
4 b's

I would also like to do this across different sheets (b$2! bit in the short
version), but this is less important as I can just change to formula slightly.

--
Phil Standen