Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
matching full name to 'two column' name using sumproduct | Excel Worksheet Functions | |||
SUMPRODUCT COUNTIF (Again) | Excel Worksheet Functions | |||
sumproduct with partial charcter matching | Excel Worksheet Functions | |||
Sumproduct or countif or both... | Excel Discussion (Misc queries) | |||
SumProduct or CountIf | Excel Worksheet Functions |