Sum if corresponding row contains text string

[webharvest]

I've tried several different things but I can't figure this one out. I have a list of companies in column A. The text in column A has the company name followed by its geographic location. Column H has a project value amount. I want to create a formula that will look at each row in column A for the company name no matter the geographic location. If column A contains the company name you will sum the corresponding project value in Column H. Here is an example:

Column A Column H
Coke - Saint Paul $6,000
Pepsi - CA $2,000
Coke - Woodbridge, VA $10,000
Coke - Cincinnati $0

So I would like the formula to sum all the values in H where the corresponding A value contains the word Coke. For this example the formula would return the value of $16,000

Thank you for any help you can provide.

[ChemistB]

Assuming you have a space before the - like your example and that there are no dashes in your company names
=SUMPRODUCT(--(MID($A$2:$A$500,1, SEARCH("-",$A$2:$A$500)-2)=$D$1),$H$2:$H$500) where D1 contains the company name
Does that work for you?

[Ron Coderre]

Try something like this:

=SUMIF(A1:A20,"*coke*",H1:H20)

or...with COKE in cell L1

=SUMIF(A1:A20,"*"&L1&"*",H1:H20)

Does that help?

[webharvest]

I came up with a solution so but I need one small tweak to make it better. Here is my Solution
=SUMIF(A:A,"*Coke*",H:H)

I would like to replace the word Coke with a cell value. i.e. in B2 I have the word Coke and I want to put the above formula in C2. I tried this formula in C2 =SUMIF(A:A,"*B2*",H:H) but it doesn't work. I thought it would return the same value as the first formula since B2 has the work Coke in it.

[ChemistB]

Try

=SUMIF(A:A,"*"&B2&"*",H:H)