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")).
Thanks Jase
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