Excel SUMPRODUCT, but matching like COUNTIF
 

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

Excel SUMPRODUCT, but matching like COUNTIF
 

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

"Phil Standen" wrote:

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

Excel SUMPRODUCT, but matching like COUNTIF
 

Using SUMPRODUCT

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


--
HTH

-------

Bob Phillips
"Phil Standen" wrote in message
...
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

Excel SUMPRODUCT, but matching like COUNTIF
 

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

"sebastienm" wrote:

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

"Phil Standen" wrote:

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

Excel SUMPRODUCT, but matching like COUNTIF
 

Thanks, works a treat.

For anyone else that is looking through:

Final formula:

=SUMPRODUCT(INDIRECT("'"&C$1&"'!$E$8:$E$100")*((IS NUMBER(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.


"Bob Phillips" wrote:

Using SUMPRODUCT

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


--
HTH

-------

Bob Phillips
"Phil Standen" wrote in message
...
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