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
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"))
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 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Type | Code | Freq | ||||
2 | A | 1042-QSJ-MAN-L14-PR014 | A & QSE or CHO | ||||
3 | A | 1042-QSE-MAN-16F-WI012 | |||||
4 | A | 1042-QSE-MAN-L04-PR004 | |||||
5 | C | 1042-GSV-BLD-EFC-BD006 | |||||
6 | B | 1042-QSJ-MAN-13D-EN085 | |||||
7 | A | 1042-CHO-WRP-L01-WI033 | |||||
8 | A | 1042-QSE-MAN-L3C-RT032 | |||||
9 | C | 1042-QSE-MAN-L2D-RT047 | |||||
10 | B | 1042-QSE-MAN-L01-DI009 | |||||
11 | B | 1042-QSE-MAN-L3D-TE011 | |||||
12 | C | 1042-QSE-MAN-L3B-FR013 | |||||
13 | A | 1042-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"))