SUMPRODUCT PROBLEM

Ian Betteridge

Active Member
Joined
Mar 25, 2006
Messages
472
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
Joined
Feb 14, 2002
Messages
85,203
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
Joined
Mar 25, 2006
Messages
472
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
Joined
Feb 14, 2002
Messages
85,203
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"))
 

Watch MrExcel Video

Forum statistics

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

Which adblocker are you using?

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
Top