Extracting text from a string

  • Thread starter Thread starter ric
  • Start date Start date
R

ric

Hi,

Can anyone help me with a formula to look through a string and find a word
and then add up the number it finds in a column next to the string
containing the word.

eg. find Ric

Col A Col B
`Ric 1000
Bob 1200
Ric 1100
Dave 950
Ric` 1050

So it would report a total for Ric as 3150
Column A and B are on sheet1 and word to search for comes from sheet2.

Many thanks

Ric
 
Thank you for the quick reply but it doesn't quite work. If I choose
autofilter then contains Ric it gives me 3 entries. The answer from the
formula you supplied seems find the first one then take that as the answer
and miss the lower two strings that contain Ric even though I've told it to
make the range go further than the last entry. And some of the names i'm
looking for from other cells are not finding the string at all even though
it's finding answers when I do a contains from autofilter.

Any chance of emailing you the file (34KB) for you to make sure i didn't
muck it up?

Ric
 
This worked for me to pick up those funny characters surrounding Ric:

=SUMIF(A1:A5,"*ric*",B1:B5)

So if "ric" were in a cell, you could use:

=SUMIF(A1:A5,"*" & C1 & "*",B1:B5)
 
Perfect, thanks Dave.

Ric
Dave Peterson said:
This worked for me to pick up those funny characters surrounding Ric:

=SUMIF(A1:A5,"*ric*",B1:B5)

So if "ric" were in a cell, you could use:

=SUMIF(A1:A5,"*" & C1 & "*",B1:B5)
Click to expand...
 
Back
Top