Count for Partial Text Match

[Flapjack]

I want a function that will add to a total number based on a partial text match.

Column B cells will have a quantity entered while column H cells will have text. If the text has "BED" in the string, then I want to multiply the number in column B (same row) by 2 and add to the total. If the text has "OED" in the string, then I want to multiply the number in column B by 1 and add to the total. My range is going to be rows 11:35.

Example:

Column A ......... Column H
.....3 .............. "Example BED"
.....7 .............. "Example BBB"
.....4 .............. "Example OED"

My Total would equal 10 (BED is a match so 2*3=6, and OED is a match so 1*4=4. 6+4=10)

I've tried to search other posts, and I think I need =SUMPRODUCT along with something like ISNUMBER(FIND("BED",H11:H35)) but I can't quite put it together.

Thanks for any help!
- Mark

 

[Brian from Maui]

Can you clarify the partial text match. Looking for bed will not find oed

 

[Oaktree]

Maybe:

=SUMPRODUCT(--ISNUMBER(SEARCH("ED",H11:H35)),A11:A35,B11:B35)

Where B11:B35 have the 2 (for BED) and 1 (for OED) values you're referring to ?

 

[Flapjack]

I don't want "BED" to find "OED" or vice versa.

The user enters a number quantity in column A (somewhere between rows 11 and 35). They then enter some sort of text on the same row in column H. If, and only if, the text contains in it "BED", then I want whatever number they entered in column A to be multiplied by the number 2 and added to my total box (where the function is). If the text happens to contain the text "OED", then I want whatever number they entered in column A to be multiplied by the number 1 and added to my total box. If neither "BED" or "OED" is within the string, then nothing happens.

 

[Oaktree]

Try setting your total =

SUMIF(H11:H35,"*OED*",A11:A35)+SUMIF(H11:H35,"*BED*",A11:A35)*2

 

[Flapjack]

Perfect.

THANKS!