Alteration reqd. to SUMPRODUCT

[Ian Betteridge]

Evening all,
In the table shown below I need to alter the formula so the SUMPRODUCT only sums QSE or CHO when they appear in column B in the 6th 7th & 8th characters within a particular cell. In the example shown I want cells B2 & B8 to comply but not B5 (1042-LAY-CHO-27D-WT018). This is because the CHO is not the 6th 7th & 8th characters within that cell.

SUMPRODUCT FUNCTION.xls
	A	B	C	D	E
1	Order Type	FunctLocation	Room		101
2	PM01	1042-QSE-PCK-L20-PK005	101		3
3	PM01	1042-QSH-PCK-26D-CV728	102
4	PM01	1042-QSH-PCK-15F-EC012H	101
5	PM01	1042-LAY-CHO-27D-WT018	101
6	PM01	1042-QSH-PCK-15F-EC012H	101
7	PM01	1042-WHP-LAY-L01-WH014R	102
8	PM01	1042-CHO-SRV-L01-AC040	101
Sheet3

Formula in cell E2 is:
=SUMPRODUCT(--($A$2:$A$9996="PM01"),--(ISNUMBER(SEARCH("QSE",$B$2:$B$9996))+ISNUMBER(SEARCH("CHO",$B$2:$B$9996))>0),--($C$2:$C$9996=E$1))

Any help is greatly appreciated.
Ian

 

[Domenic]

Try...

=SUMPRODUCT(--($A$2:$A$9996="PM01"),--ISNUMBER(MATCH(MID($B$2:$B$9996,6,3),{"QSE","CHO"},0)),--($C$2:$C$9996=E$1))

Hope this helps!

 

[Ian Betteridge]

Thanks Domenic
Another problem sorted thanks to this superb website and it's very helpful members.