Excel SUMPRODUCT, but matching like COUNTIF

[Guest]

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.

 

[Guest]

Hi Phil,
Try:
=SUMIF( $F$8:$F$100 , "*" & $A2 & "*" , $E$8:$E$100 )
Regards,
Sebastien

 

[Bob Phillips]

Using SUMPRODUCT

=SUMPRODUCT(--(ISNUMBER(FIND(A2,INDIRECT(B2&"!F8:F100")))),INDIRECT(B2&"!E8:
E100"))

 

[Guest]

I did, the $F$8:$F$100 , "*" & $A2 & "*" bit gives an array of #VALUE?'s.

 

[Guest]

Thanks, works a treat.

For anyone else that is looking through:

Final formula:

=SUMPRODUCT(INDIRECT("'"&C$1&"'!$E$8:$E$100")*((ISNUMBER(FIND(Summary!$B2,INDIRECT("'"&C$1&"'!$F$8:$F$100"))))-(ISNUMBER(FIND(Summary!$B2&"
",INDIRECT("'"&C$1&"'!$F$8:$F$100"))))))

That is 'count occurances of "foo" but not if it is followed by a space then
multiply it by the weight where "foo" is in col B, looking on worksheet in
row 1'.

Doesn't exactly answer the Match like countif functionality (I don't think
you can use 'a*a', for example, and the search text) but does what I want.