Tracking if projects are late or on time

[YZ5550]

Hello:

I have a spread sheet recording over 1000 projects' complete date in column A and the due date for those projects are in column B.

I want users to be able to enter a starting date (cell C1) and an end date (cell C2), and return a number showing how many projects that were completed between those two dates are on time.

Thanks!

[K m]

Where is your sample data?
===================
You could add a helper column and compare the completed time and the due date
====================
You also accomplish this with conditional formatting. The conditional formatting would "flag" those that did not meet your criteria

[Moo the Dog]

Try this formula:

Please Login or Register  to view this content.

I have "comp date" in cell A1, and "due date" in B1, in case you were wondering why I started the ranges at A2 and B2.

- Vince

[YZ5550]

It worked! Thanks! But could you please explain to me what's the "*" is for in the function? How does this array assigning work logically?

[Moo the Dog]

Basically, when you use the SUMPRODUCT function, each calculation, for example (A2:A1000>$C$1), returns a TRUE/FALSE value. By multiplying the calculations it assigns each TRUE or FALSE a 1 or 0. Thus, for each calculation, it will only add 1 to the sum if ALL conditions are true [a typical 'AND' operation] (1*1*1) = 1. If any of the calculations result in a 0 (False), then it won't add anything to the sum (1*0*1) = 0.

There are many more elegant explanations of SUMPRODUCT, but that is how I see it in my head.

- Vince

[YZ5550]

Ahh I see.

Now what if I want to find out the average of how many days are the late projects late by, how do I do that?

Thanks!