Count a Matching criteria to a date range

Afternoon all,

I'm trying to work out how to add a date range criteria to this SUMPRODUCT formula

=SUMPRODUCT(('Planning Data'!$A$1:$A$10000="1A")*('Planning Data'!$E1:$E10000={"AADP","AM","EAA","PAA","RS","RSNR","S","SAM","SAMT","SAO","SAT","SOR","WKS"}))

As is, this formula returns the result of how many work orders within a certain area are planned based on statuses. I'd like to be able to add another criteria to show the age of these planned work orders.

I was looking at less than 30 days, 31 - 90 days and plus 90 days. A different formula for each is fine.

Thanks for the help all!

Try:

=SUMPRODUCT(('Planning Data'!$A$2:$A$10000="1A")*('Planning Data'!$E2:$E10000={"AADP","AM","EAA","PAA","RS","RSNR","S","SAM","SAMT","SAO","SAT","SOR","WKS"})*('Planning Data'!$J$2:$J$10000<=TODAY()+30))

Notice starting at row 2 since J1 is text and that would cause error.


similarly:

=SUMPRODUCT(('Planning Data'!$A$2:$A$10000="1A")*('Planning Data'!$E2:$E10000={"AADP","AM","EAA","PAA","RS","RSNR","S","SAM","SAMT","SAO","SAT","SOR","WKS"})*('Planning Data'!$J$2:$J$10000>=TODAY()+31)*('Planning Data'!$J$2:$J$10000<=TODAY()+90))

etc

NBVC,

Thanks for the reply. I worked with your suggestions a bit yesterday and could not get them to work correctly. I've re-attached a workbook where I've used your formulas. H2:F11 have the formulae and I2:K11 have what the correct results should be.

Please note that the criteria string inside the SUMPRODUCT function was changed from

"AADP","AM","EAA","PAA","RS","RSNR","S","SAM","SAMT","SAO","SAT","SOR","WKS"

to

"AB","AE","AO","AWE","AWI","AWT","BLC","CAN","DFRD","FC","FCAA","FCHP","RP"

Ok, for the Under 30 days.. I didn't know you probably wanted from today to 30 days in the future...

I would amend the formula to:

=SUMPRODUCT(('Planning Data'!$A$2:$A$10000="1A")*('Planning Data'!$E2:$E10000={"AB","AE","AO","AWE","AWI","AWT","BLC","CAN","DFRD","FC","FCAA","FCHP","RP"})*('Planning Data'!$J$2:$J$10000>=TODAY())*('Planning Data'!$J$2:$J$10000<=TODAY()+30))

But I get 16 and have filtered the main database based on your conditions and drilled down to the 16.

How do you get 0?

NBVC

Thanks again for the help. your last comment

I didn't know you probably wanted from today to 30 days in the future...

and the fact that you drilled down within the data and had a different result caused me to look a little harder at your original post. I never specified which column to use as the date criteria and you went with J:J which was the basic finsih date. I was looking at what age a WO was based on the created on date in K:K. I went back and tweaked the original spreadsheet using the correct column references and everything appears fine now.

For anyone who may have been looking at this post the final formulas are as follows. For WO's created from today to 30 days in the past

=SUMPRODUCT(('Planning Data'!$A$2:$A$10000="1A")*('Planning Data'!$E2:$E10000={"AB","AE","AO","AWE","AWI","AWT","BLC","CAN","DFRD","RP"})*('Planning Data'!$K$2:$K$10000<=TODAY())*('Planning Data'!$K$2:$K$10000>=TODAY()-30))

for WO's created from 31 to 90 days ago

=SUMPRODUCT(('Planning Data'!$A$2:$A$10000="1A")*('Planning Data'!$E2:$E10000={"AB","AE","AO","AWE","AWI","AWT","BLC","CAN","DFRD","FC","FCAA","FCHP","RP"})*('Planning Data'!$K$2:$K$10000>=TODAY()-31)*('Planning Data'!$K$2:$K$10000<=TODAY()-90))

and for WO's created 91 days ago or more

=SUMPRODUCT(('Planning Data'!$A$2:$A$10000="1A")*('Planning Data'!$E2:$E10000={"AB","AE","AO","AWE","AWI","AWT","BLC","CAN","DFRD","FC","FCAA","FCHP","RP"})*('Planning Data'!$K$2:$K$10000>=TODAY()-31)*('Planning Data'!$K$2:$K$10000<=TODAY()-90))

I should have also stated that assumption...

It's all good and the help is truely appreciated! Have a great day!