Calculating how many entries are 0-7, 8-29, 30-60,etc days old

[rstotty]

My spreadsheet contains:
Column B: 3 types of status entries - (Pending,Active,Scheduled)
Column C: Dates
I need to calculate how many entries by status in column B are 0-7 days old, 8-29 days old, 30-60 days old, 61-90 days old and >90 days old. Any help would be greatly appreciated.

 

[oldchippy]

Hi rstotty,

Welcome to he forum, can you please upload a sample of your spreadsheet?

http://chandoo.org/forum/threads/posting-a-sample-workbook.451/#post-73705

 

[rstotty]

Gladly.

 

[oldchippy]

Hi

Why not select your data in columns A1:C1852, then go to Insert > Pivot Table
Put "Status" in column labels, "Urgency" in row labels, then drag "File Entry" into values to do a count.
This will then show all in the different urgencies

 

[rstotty]

I tried that but it still doesn't give me what I'm looking for. I need to know how many "pendings" are 0-7 days old, 8-29 days old, 30-60 days old, etc. I'm trying to use "COUNTIFS" to count the number of "pendings" but I cant figure out how to break it down by the range of days.

 

[rstotty]

Okay. I think I have it. I added additional columns with the date ranges and created a formula to calculate what I need. Rather than try to explain it, I'll attach the file and you can see what I did.

 

[Hui]

Without additional columns see attached

 

[Debraj]

Here Another approach..

=INDEX(FREQUENCY(IF(STATUS=$E12,(TODAY()-FILE_ENTRY)),$F$11:$J$11),COLUMN(A$1))

with Ctrl + Shift + Enter..

 

[rstotty]

Fantastic!! Thank you all for you help.