How many specific things are overdue AND by how much

[chalmers]

Example Problem.xlsxHi Guys and Gals,

Just making my own review program/little project at work and need some tips for some formulas that would make my spreadsheet complete.
I will attach a spreadsheet that will show you what I am rambling on about.

Looking at the Attached Spreadsheet:

1. I have already used the COUNTIF Function to determine how many items from the "Package 1 Details" (Sheet 2) go into which status heading of the "Report" (Sheet 1).

I.e. For Package 1:
Submission (B3) =COUNTIF('Package 1 Details'!B4:B21,"Submittal")
Processing (C3) =COUNTIF('Package 1 Details'!B4:B21,"Processing")
Review (D3) =COUNTIF('Package 1 Details'!B4:B21,"Review")
Review Processing (E3) =COUNTIF('Package 1 Details'!B4:B21,"Review Processing")
Resubmission (F3) =COUNTIF('Package 1 Details'!B4:B21,"Resubmittal")
Completed Items (G3) =COUNTIF('Package 1 Details'!B4:B21,"Complete")

That's the easy part done....

2. What I would like to do now is to go back to the "Package 1 Details" (Sheet 2) and determine how many of the individual status' are overdue, AND by how much. I would like to have Status overdue by 1-7 days, 7-14 days and 14+ days according to the Review Date (H1).

Obviously, I do not need to worry about the Completed Items so that leaves 5 Status'.

(Submission/Processing/Review/Review Processing/Resubmission)
(1-7 Days Overdue/7-14 Days Overdue/14+ Days Overdue)

For example, Submission: 1-7 Days Overdue - I have a feeling it would be something along the lines of:

=COUNTIF('Package 1 Details'!B4:B21,"Submittal")AND(Today(Report!H1)-"'Package 1 Details'!A4:A21"<7))

3. Could you please advise the correct formulas I should use to determine the said days overdue for each specific status?

Kind Regards,

Chalmers

[benishiryo]

hi chalmers, 2 things i wanna ask:

1) is overdue based on Review Date > Initial Submittal Date or the other way round? I saw u put in your formula

Today(Report!H1)-"'Package 1 Details'!A4:A21"<7

so i assumed it's the former?

2) Your Overdue Categories overlap each other. 1-7 includes 7. 7-14 days includes 7 too. It also includes 14, along with 14 days Overdue.

My formula is based on the assumption it's Review Date - Initial Submittal Date. And the categories are:
1-7
8-14
15 & above

See if that is what u need

[chalmers]

benishiryo,

Thanks a lot for this, it works brilliantly.

1) correct assumption. Thanks for bearing with my capabilities of excel.

2) ^ thanks again.

3) Adding on to this, if I wanted to add a second sheet, or more, I'm fairly confident I'm getting a correct output if I simply add the two together (making sure I have correct brackets lol):

=SUMPRODUCT(('Package 1 Details'!$B$4:$B$21="Submittal")*($H$1-('Package 1 Details'!$A$4:$A$21)<=7))+(SUMPRODUCT(('Package 2 Details'!$B$4:$B$21="Submittal")*($H$1-('Package 2 Details'!$A$4:$A$21)<=7)))

I'll change this thread to solved, because I think I have it correct, but may come back if I have problems with this.

Thanks again benishiryo.

Chalmers

[benishiryo]

=) not a problem. glad it works. the added formula looks fine i think. just have to make sure the range is still valid.