Counting Dates Older than AND Less Than

[doverfelt]

I have a workbook where I need to count the number of Requests that are pending and how long they have been waiting. I have two columns; Status of Request and Date Submitted. I need to identify those requests in PENDING status, then determine how many of them are less than (<)90 days old, how many are greater than (>) 90 AND less than (<) 180, and how many are MORE than 180 days old, etc

My table contains entries like this, and is around 600 rows long.

Column P Column Q
Submitted Status
Request of
Date Request

4/27/2017 Accepted
7/31/2017 Pending
3/31/2017 Pending
9/29/2016 Accepted
5/2/2016 Accepted
3/8/2017 Pending
etc. etc.

So, for my formula, I first need to identify those rows where the Status = Pending, then, determine how old the request is. I started with a COUNTIF formula using the ()Today function as a starting point. This function tells me how many requests are MORE THAN 365 days old...
=COUNTIF(P:P,"<"&(TODAY()-365))
But, this is going to need to be NESTED within an IF formula first, then have the range of days; ie >90 AND <120, etc. Does this make sense? I've tried playing with this numerous ways with no luck, and have searched this forum for answers. I think I'm using the wrong search words to be honest. Here's the logic behind what I need to do...

IF STATUS = PENDING, COUNTIF(SUBMITTED DATE is LESS THAN 90 Days
-COUNTIF(SUBMITTED DATE is MORE THAN 90 Days AND LESS THAN 180 Days
-COUNTIF(SUBMITTED DATE is MORETHAN 180 Days AND LESS THAN 270 Days
-COUNTIF(SUBMITTED DATE IS MORE THAN 270 Days AND LESS THAN 365 Days
- COUNTIF(SUBMITTED DATE IS MORE THAN 365 Days
Using the columns in this...
IF(Q:Q,"Pending",(COUNTIF(P:P,"<"&(TODAY()-90
-(COUNTIF(P:P,">"&(TODAY()-90 AND "<"&(TODAY()-180
-(COUNTIF(P:P,">"&(TODAY()-180 AND "<"&(TODAY()-270
-(COUNTIF(P:P,">"&(TODAY()-270 AND "<"&(TODAY()-365
-(COUNTIF(P:P,">"&(TODAY()-365

I think this logic is correct, but getting it laid out and working is a different matter. Can any help me out on this one?

DOVERFELT

 

[doverfelt]

This post did not layout like I expected... the section on my table layout isn't right.. it really should be...
Column P is Submitted Date
Column Q Status of the Request

4/27/2017 Accepted
7/31/2017 Pending
3/31/2017 Pending
9/29/2016 Accepted
5/2/2016 Accepted
3/8/2017 Pending
etc. etc.

 

[Joe4]

Re-posted here: https://www.mrexcel.com/forum/excel-questions/1021383-using-countif-dates.html

Please do not post the same question multiple times. In the future, please post all clarifications, directly-related follow-ups, and bumps back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html).