# Count for Partial Text Match

#### Flapjack

##### Board Regular
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

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Can you clarify the partial text match. Looking for bed will not find oed

Maybe:

Code:
``=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 ?

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.

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

Perfect.

THANKS!

Replies
3
Views
401
Replies
3
Views
651
Replies
1
Views
330
Replies
9
Views
749
Replies
1
Views
321

1,203,691
Messages
6,056,756
Members
444,889
Latest member
ibbara

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back