Complex array formula

[Spraggs]

This is way out of my league.....
In the table shown the formula below counts the number of times "QSE" or "CHO" appear in column F when 1042 appears in column D. What I want to do is modify this formula so it counts as above but only the data in column B is within the last 7 days.(TODAY-7).
I then want another formula that counts as above but only the date in column B is older than 7 days.
Array formula
=SUMPRODUCT(--ISNUMBER(MATCH(MID($F$2:$F$1000,6,3),{"QSE","CHO"},0)),--($D$2:$D$1000="1042")).

TEST SHEET.xls
	A	B	C	D	E	F	G	H
1	Created by	Created on	Notification	Planning plant	Description	FunctLocation	System status	Room
2	UKFAIRCLKE	02/07/2007	10812108	1001	unit on line U/S spare unit faulty	1001-MIN-MAN-M11-MXP111	NOPR	LINE 11
3	UKMCCAMBR	05/07/2007	10819243	1042	faulty av 325	1042-QSJ-MAN-L02-EC206J	OSNO	HANDLGJ
4	UKPEARMATB	10/07/2007	10824357	1001	(service) steam leak, T/P	1001-MIN-MAN-FON-CNN103	NOPR	FONDANT
5	UKJLEWIS	16/07/2007	10833748	1042	Toffo sterilant pump	1042-TFO-SRV-L01-PZ030	OSNO	GENKFACT
6	UKNAWROCTO	19/07/2007	10839104	1042	air con leaking water	1042-GSV-HEN-CLG-AC094	NOPR	GENSITE
7	UKBROWNRPA	20/07/2007	10840451	1042	bulbs need replacing on panel	1042-QSH-PCK-27F-EC042H	OSNO	SMALLTIN
8	UKPEARMATB	25/07/2007	10846473	1001	(mech) Drive shaft. T/P	1001-MIN-PKW-OUT-TGW146	NOPR	F/ROOM
9	UKELLISDE	01/08/2007	10856353	1042	lowerator interlock on guard	1042-QSH-PCK-26E-EC052H	NOPR	BULKQSH
10	UKFORSTEMA	06/08/2007	10863025	1042	worn exit belt on lid unscrambler	1042-QSH-PCK-27F-EC085H	NOPR	SMALLTIN
11	UKMYERSNI	07/08/2007	10863637	1042	MB009 Shows 9kgs when vessel is empty	1042-QSJ-MAN-L01-MB009	OSNO	PR26
12	UKWISNIERA	07/08/2007	10863739	1042	Worn drive belts needs replacing	1042-QSH-PCK-26E-EC010H	NOPR	BULKQSH
13	UKBETTERIA	29/08/2007	10890923	1001	(Mech) Conveyor bearing collapsed ?	1001-MIN-PKW-OUT-TGW146	NOPR	F/ROOM
14	UKMYERSNI	05/09/2007	10899684	1042	unable to caustic Clean MB009	1042-QSJ-MAN-L01-MB009	OSNO	PR26
15	UKSMITHGA1	06/09/2007	10901956	1042	Place Text Here	1042-QSJ-MAN-12A-CS061	OSNO	RNDMFR
16	UKWOODMI1	09/09/2007	10904702	1042	Pneumatic tubing trailing on floor	1042-QSJ-MAN-13D-EN085	NOPR	OVALMFR
17	UKMYERSNI	12/09/2007	10909992	1042	HK133 Shows 12kgs when vessel is empty	1042-QSJ-MAN-13A-HK133	OSNO	OVALMFR
Job List

Thanks Jase

 

[mikerickson]

You want to add the conditions that
B2>(TODAY()-7) or B2>=(TODAY()-7)

So the formulas would be

=SUMPRODUCT(--($B$2:$B$1000>(TODAY()-7),--ISNUMBER(MATCH(MID($F$2:$F$1000,6,3),{"QSE","CHO"},0)),--($D$2:$D$1000="1042"))

=SUMPRODUCT(--($B$2:$B$1000<=(TODAY()-7),--ISNUMBER(MATCH(MID($F$2:$F$1000,6,3),{"QSE","CHO"},0)),--($D$2:$D$1000="1042"))

 

[gwkenny]

Can give this a shot.

=SUMPRODUCT(--ISNUMBER(MATCH(MID($F$2:$F$1000,6,3),{"QSE","CHO"},0)),--($D$2:$D$1000="1042"),--($B$2:$B$1000<=NOW()-7)).

Hope it helps.

 

[gwkenny]

Ahhh

Mike's use of the TODAY() function probably serves your purposes (and most people's) better. I'm too use to working with people who need the NOW() function.

Thus:

=SUMPRODUCT(--ISNUMBER(MATCH(MID($F$2:$F$1000,6,3),{"QSE","CHO"},0)),--($D$2:$D$1000="1042"),--($B$2:$B$1000<=TODAY()-7)).

 

[Spraggs]

Thanks guys, I'll this a go.
Jase

 

[Spraggs]

Your answers worked perfect, could I ask your assistance to adapt this a bit further?
Formulas to give:
7-14 days old
14-28 days old.
I've used one the formulas to give me 28 days +
and 0 -7 days old.
Thanks again.

Test page.xls
	A	B	C	D	E	F	G	H	I	J	K
1	Created by	Created on	Notification	Planning plant	Description	FunctLocation	System status	Room			E FACTORYQSE / CHO
2	UKFAIRCLKE	02/07/2007	10812108	1001	unit on line U/S spare unit faulty	1001-MIN-MAN-M11-MXP111	NOPR	LINE 11		0 - 7 DAYS	1
3	UKMCCAMBR	05/07/2007	10819243	1042	faulty av 325	1042-QSJ-MAN-L02-EC206J	OSNO	HANDLGJ		7 - 14 DAYS
4	UKPEARMATB	10/07/2007	10824357	1001	(service) steam leak, T/P	1001-MIN-MAN-FON-CNN103	NOPR	FONDANT		14 - 28 DAYS
5	UKJLEWIS	16/07/2007	10833748	1042	Toffo sterilant pump	1042-TFO-SRV-L01-PZ030	OSNO	GENKFACT		28 DAYS +	27
6	UKNAWROCTO	19/07/2007	10839104	1042	air con leaking water	1042-GSV-HEN-CLG-AC094	NOPR	GENSITE
7	UKBROWNRPA	20/07/2007	10840451	1042	bulbs need replacing on panel	1042-QSH-PCK-27F-EC042H	OSNO	SMALLTIN
8	UKPEARMATB	25/07/2007	10846473	1001	(mech) Drive shaft. T/P	1001-MIN-PKW-OUT-TGW146	NOPR	F/ROOM
9	UKELLISDE	23/01/2008	10856353	1042	lowerator interlock on guard	1042-QSE-PCK-26E-EC052H	NOPR	BULKQSH
10	UKFORSTEMA	06/08/2007	10863025	1042	worn exit belt on lid unscrambler	1042-QSH-PCK-27F-EC085H	NOPR	SMALLTIN
11	UKMYERSNI	07/08/2007	10863637	1042	MB009 Shows 9kgs when vessel is empty	1042-QSJ-MAN-L01-MB009	OSNO	PR26
12	UKWISNIERA	07/08/2007	10863739	1042	Worn drive belts needs replacing	1042-QSH-PCK-26E-EC010H	NOPR	BULKQSH
13	UKBETTERIA	29/08/2007	10890923	1001	(Mech) Conveyor bearing collapsed ?	1001-MIN-PKW-OUT-TGW146	NOPR	F/ROOM
14	UKMYERSNI	05/09/2007	10899684	1042	unable to caustic Clean MB009	1042-QSJ-MAN-L01-MB009	OSNO	PR26
15	UKSMITHGA1	06/09/2007	10901956	1042	Place Text Here	1042-QSJ-MAN-12A-CS061	OSNO	RNDMFR
16	UKWOODMI1	09/09/2007	10904702	1042	Pneumatic tubing trailing on floor	1042-QSJ-MAN-13D-EN085	NOPR	OVALMFR
17	UKMYERSNI	12/09/2007	10909992	1042	HK133 Shows 12kgs when vessel is empty	1042-QSJ-MAN-13A-HK133	OSNO	OVALMFR
18	UKSHACKLD	19/09/2007	10919122	1042	pl091	1042-QSJ-MAN-L01-PL091	OSNO	PR26
19	UKMYERSNI	20/09/2007	10920571	1042	Modification of HK130 Vessel Lid Opener	1042-QSJ-MAN-13A-HK130	OSNO	OVALMFR
20	UKBICKNERO	24/09/2007	10925414	1042	hose handle leaking cream room	1042-QSJ-SRV-L01-PZ032	OSNO	GENJBOT
21	UKGILLCO	25/09/2007	10927676	1042	Condensation dripping from stop valve	1042-QSJ-MAN-14G-AC130	NOPR	ECLRWPG
22	UKROSEHE	27/09/2007	10930230	1042	glacier bushes need replacing	1042-QSJ-MAN-13G-WI027	NOPR	OVALWPG
23	UKWALSHAAD	04/10/2007	10940397	1042	Removal of camera	1042-QSH-PCK-18F-EC387H	NOPR	KTB18
24	UKPEACOCAL	15/10/2007	10954417	1042	Not adding sterilant to CIP	1042-TFO-SRV-L01-PZ030	NOPR	GENKFACT
25	UKNEWTONJO	26/10/2007	10972677	1042	drive roller sliping pr12 last 2 m/c's	1042-QSJ-MAN-12E-CV444	NOPR	RNDMFR
26	UKTORCZYJA	31/10/2007	10978115	1042	air con leaking online 27	1042-GSV-HEN-CLG-AC104	NOPR	GENSITE
Sheet2

 

[mikerickson]

AND(TODAY()-7>B2,B2>=TODAY()-14) = TRUE
when if the date B2 is between 7 and 14 days ago.

 

[Spraggs]

I'm a bit lost with this. Is it possible you could put the full formula so I could cut & paste?
If I write todays date instead of using TODAY the formula now longer works, how would this be written?
Your time is very much appreciated.
Regards Jase

 

[barry houdini]

You can't use AND with arrays try this

=SUMPRODUCT(--ISNUMBER(MATCH(MID($F$2:$F$1000,6,3),{"QSE","CHO"},0)),--($D$2:$D$1000="1042"),--($B$2:$B$1000>TODAY()-14),--($B$2:$B$1000<=TODAY()-7))

Note: this formula doesn't need CRL+SHIFT+ENTER

 

[mikerickson]

My bad.
AND isn't used with SUMPRODUCT, one needs to multiply to get the same result.

=SUMPRODUCT(--((TODAY()-7>$B$2:$B$1000)*($B$2:$B$1000>=TODAY()-14)),--ISNUMBER(MATCH(MID($F$2:$F$1000,6,3),{"QSE","CHO"},0)),--($D$2:$D$1000="1042"))