Find %ontime & SUMIF ontime ie: find matching sets within Range...

[Chris T-M]

I tried to simplify this question, but I think the real problem is as simple
as this is going to get. Probably why I couldn't find an answer.

I'm looking for dates that are due & complete to...
1) compare projects due&delivered / projects due (%)
2) compare all projects delivered / projects due (%)
3) add up the value of all due projects. ($)
4) add up the value of all delivered projects. ($)

I don't want to enter functions row by row and then COUNTIF... I'm working
from another worksheet, and don't want to add Columns into the Data Set I'm
searching.

I want to compare 3 ranges to find what's due in the specified month, and if
they were on-time or early.

I have 6 cells that calculate the 1st and last day of 'last month', 'this
month', 'next month' that I'm using to set my lookup ranges, but I can't
count up the matches yet.

I have successfully calculated items that are 'due' using
=SUMPRODUCT(('PROJECT LIST'!$Y$49:$Y$240>=$AD$2)*('PROJECT
LIST'!$Y$49:$Y$240<=$AE$2))

I have 6 columns of data, or more specifically 3 sets of 2... A&B, C&D, E&F
(each set referring to a project phase)

A B C D E F G
Due Actual Due Actual Due Actual Value
03/20/08 02/05/08 04/10/08 04/10/08 05/15/08 04/30/08 $10
03/06/08 03/06/08 04/15/08 04/20/08 06/15/08 $50
04/10/08 03/20/08 04/20/08 05/20/08 $20

Anticipated output assuming it's the end of April...
Last Month (How did we do in March?)
1) 2 due & complete / 2 due = 100% of committed
2) 3 delivered / 2 due = 150% on-time
3) $60 value due
4) $80 value progressed

This Month (How are we looking in April?)
1) 2 due & complete / 4 due = 50% of committed
2) 2 delivered / 4 due = 50% on-time
3) $80 value due
4) $30 value progressed

Next Month (What's comming in May?)
1) 1 due & complete / 2 due = 50% of committed (so far)
2) 1 delivered / 2 due = 50% on-time (Hows the remaining project looking?)
3) $30 value due
4) $10 value already delivered

 

[Sean Timmons]

So, for due&delivered / due just have

=sumproduct(--(A2:F5000>=start date),--(A2:F5000<=end
date))/(sumproduct(--(B2:B5000>=start date),--(B2:B5000<=end
date))+sumproduct(--(D25000>=start date),--(D25000<=end
date))+sumproduct(--(F2:F5000>=start date),--(F2:F5000<=end date)))

For compare all projects delivered / projects due (%)

=(sumproduct(--(A2:A5000>=start date),--(A2:A5000<=end
date))+sumproduct(--(C2:C5000>=start date),--(C2:C5000<=end
date))+sumproduct(--(E2:E5000>=start date),--(E2:E5000<=end
date)))/(sumproduct(--(B2:B5000>=start date),--(B2:B5000<=end
date))+sumproduct(--(D25000>=start date),--(D25000<=end
date))+sumproduct(--(F2:F5000>=start date),--(F2:F5000<=end date)))

add up the value of all due projects. ($)
=sumproduct(--(A2:A5000>=start date),--(A2:A5000<=end
date),--(G2:G5000))+sumproduct(--(C2:C5000>=start date),--(C2:C5000<=end
date),--(G2:G5000))+sumproduct(--(E2:E5000>=start date),--(E2:E5000<=end
date),--(G2:G5000))

add up the value of all delivered projects. ($)
sumproduct(--(B2:B5000>=start date),--(B2:B5000<=end
date),--(G2:G5000))+sumproduct(--(D25000>=start date),--(D25000<=end
date),--(G2:G5000))+sumproduct(--(F2:F5000>=start date),--(F2:F5000<=end
date),--(G2:G5000))

 

[Chris T-M]

OK, you made that look a lot simpler than it was. There was a lot of code to
fill in, but... It worked great! Thank you very much for the detailed
response!!!

 

[Sean Timmons]

Thank you for the response!

There may have been a more concise answer, but sometimes it's better if you
understand how it works so you can do it for yourself next time.