Text string search/addition

[jerjoe]

I'd like to search column A for a specific text or number
string and add up the corresponding value(s) from column
B, but I'd like to enter what I'm looking for in C1.
Is this possible to do by a formula in D1? (or VBA?)

Cells in column A1:A100 contain general location
description with spaces, letters, and numbers or it is
left blank. (Example: 4 found behind 2nd shed on the right)
Column B1:B100 will always be a number.
C1=shed
Can I find the text string "shed" no matter where its
position is in a cell and add up all of the corresponding
values from B and put it in D1?

TIA

 

[Frank Kabel]

Hi
in D1 use
=SUMPRODUCT(--(ISNUMBER(SEARCH(C1,A1:A100))),B1:B100)

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Myrna Larson]

Formula in D1:

=SUMIF(A1:A100,"*"&C1&"*",B1:B100)