sumif based on partial match
 

I want to perform a sumif type calc based on only matching the first
few characters of my range, rather than the entire value.

e.g. find abc in the cell value abc123 and then get the value from the
next column same row and return a 0 if not found. And do this over a
column of 100 different items, some of which will have matching "abc"s,
hence the need to sum.

I have tried tinkering with a nested search function in an array but it
returns #value - is there a function equivalent to search that returns
a 0 for not found instead of #value. Or perhaps a smarter way of
dealing with the not founds??

Any other suggestions??

TIA.

sumif based on partial match
 

=SUMIF($A$2:$A$100,E2&"*",$B$2:$B$100)

where E2 houses a partial string like abc which is a condition that
A2:A100 must meet in order to sum corresponding figures from B2:B100.

wrote:
I want to perform a sumif type calc based on only matching the first
few characters of my range, rather than the entire value.

e.g. find abc in the cell value abc123 and then get the value from the
next column same row and return a 0 if not found. And do this over a
column of 100 different items, some of which will have matching "abc"s,
hence the need to sum.

I have tried tinkering with a nested search function in an array but it
returns #value - is there a function equivalent to search that returns
a 0 for not found instead of #value. Or perhaps a smarter way of
dealing with the not founds??

Any other suggestions??

TIA.

sumif based on partial match
 

I am incredulous - I cannot believe the solution is so simple -
thankyou indeed very much!