# SUMPRODUCT PROBLEM

#### Ian Betteridge

##### Active Member
Could anyone please assist?
In the sheet enclosed I want to count the number of times that QSE or CHO appears in column B but only when an A is in the corresponding row in column A. In this example the answer that should appear in cell E2 should be 5.
One thing to mention is that the letters I'm searching will always be the 6th 7th & 8th letter in the cells of column B.
Thanks in advance Ian
Book1
ABCDE
1TypeCodeFreq
2A1042-QSJ-MAN-L14-PR014A & QSE or CHO
3A1042-QSE-MAN-16F-WI012
4A1042-QSE-MAN-L04-PR004
5C1042-GSV-BLD-EFC-BD006
6B1042-QSJ-MAN-13D-EN085
7A1042-CHO-WRP-L01-WI033
8A1042-QSE-MAN-L3C-RT032
9C1042-QSE-MAN-L2D-RT047
10B1042-QSE-MAN-L01-DI009
11B1042-QSE-MAN-L3D-TE011
12C1042-QSE-MAN-L3B-FR013
13A1042-QSE-MAN-L01-DI008
Sheet1

I have an array formula from aprevious question that I've tried to adapt but unsure how to make it look for text within a string. If column B just had 3 leter entries this formula works. Could anyone please adapt it to suit my needs.
=SUMPRODUCT(--ISNUMBER(MATCH(B2:B13,{"QSE","CHO"},0)),--(A2:A13="A"))

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Aladin Akyurek

##### MrExcel MVP
Given that C2 is A and D2 QSE...

=SUMPRODUCT(--(\$A\$2:\$A\$13=C2),--(MID(\$B\$2:\$B\$13,6,3)=D2))

#### Ian Betteridge

##### Active Member
Thanks Aladin for your rapid reply. I realised that I've had to edit the original question. It's now way way over my level.
Regards Ian

#### Aladin Akyurek

##### MrExcel MVP
Thanks Aladin for your rapid reply. I realised that I've had to edit the original question. It's now way way over my level.
Regards Ian

=SUMPRODUCT(--ISNUMBER(MATCH(MID(B2:B13,6,3),{"QSE","CHO"},0)),--(A2:A13="A"))

#### Ian Betteridge

##### Active Member
Superb,
Thanks for your valued time.

Replies
9
Views
412
Replies
2
Views
392
Replies
2
Views
431
Replies
10
Views
897

Threads
1,129,568
Messages
5,637,094
Members
416,957
Latest member
Brovashift

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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