Looking for help on a count function

[joshk87]

I believe what I'm trying to accomplish is simple, but I just can't seem to get any results no matter what function I try.

Here's the scenario. I have a column labled "Date Required" (column K) and a column labled "Date Completed" (column I). I want to capture 3 things from these two columns and chart them. I would like to know the number of times a given "Date Completed" is either on time, late, or early. That way I can pie chart the percentage of jobs throughout the year that are one of those three; on time, late, or early.

I treid using countif to say that if the cells in column K is greater than column I then give me the count. Same thing goes for less than or equal to. Nothing seems to be working and I can't find out if this is even the best function to use.

Any help would be much appreciated!

 

[bryonwoods30]

If you have date completed in column I, date required in column k, use the formula

=IF(K2>I2,"LATE",IF(K2=I2,"ON TIME","EARLY"))

 

[joshk87]

If you have date completed in column I, date required in column k, use the formula

=IF(K2>I2,"LATE",IF(K2=I2,"ON TIME","EARLY"))

Thanks!, But that's not quite all that im looking for.

How would I go about tallying all the "on times" and all the "lates" in that column. That's what I really need to generate so I can chart it. I have hundreds of dates so while your formula will work for one cell, i need something that will count all of the populated cells in a column.

 

[bryonwoods30]

Just drag the formula down as far as your dates go and it will fill in the late, on time, early values.

Then make a table somewhere else like below

first column second column
EARLY =countif(highlight the entire column of your results,"EARLY")
ON TIME =countif(highlight the entire column of your results,"ON TIME")
LATE =countif(highlight the entire column of your results,"LATE")

 

[joshk87]

Just drag the formula down as far as your dates go and it will fill in the late, on time, early values.

Then make a table somewhere else like below

first column second column
EARLY =countif(highlight the entire column of your results,"EARLY")
ON TIME =countif(highlight the entire column of your results,"ON TIME")
LATE =countif(highlight the entire column of your results,"LATE")

Wouldn't that require that I have an entire column that contains the "late", "on time" and "early" output? That way the counif function has a column to count. If I do need need that column is there any other way of doing this that would not require the additional column?

 

[T. Valko]

I believe what I'm trying to accomplish is simple, but I just can't seem to get any results no matter what function I try.

Here's the scenario. I have a column labled "Date Required" (column K) and a column labled "Date Completed" (column I). I want to capture 3 things from these two columns and chart them. I would like to know the number of times a given "Date Completed" is either on time, late, or early. That way I can pie chart the percentage of jobs throughout the year that are one of those three; on time, late, or early.

I treid using countif to say that if the cells in column K is greater than column I then give me the count. Same thing goes for less than or equal to. Nothing seems to be working and I can't find out if this is even the best function to use.

Any help would be much appreciated!

Maybe these...



I'm assuming that these are the definitions of your criteria:

• on time: the dates in both columns are the same
• late: the date completed is greater than the date required
• early: the date completed is less than the date required

On Time:

=SUMPRODUCT(--(I2:I10=K2:K10))

Late:

=SUMPRODUCT(--(I2:I10>K2:K10))

Early:
<?XML:NAMESPACE PREFIX = K2 /><K2:K10))< p></K2:K10))<>
<K2:K10))< p>=SUMPRODUCT(--(I2:I10 < K2:K10))

Assuming there are no empty cells.
</K2:K10))<>

 

[joshk87]

Maybe these...




I'm assuming that these are the definitions of your criteria:

• on time: the dates in both columns are the same
• late: the date completed is greater than the date required
• early: the date completed is less than the date required

On Time:

=SUMPRODUCT(--(I2:I10=K2:K10))

Late:

=SUMPRODUCT(--(I2:I10>K2:K10))

Early:
<?XML:NAMESPACE PREFIX = K2 /><K2:K10))< p></K2:K10))<>
<K2:K10))< p>=SUMPRODUCT(--(I2:I10 < K2:K10))

Assuming there are no empty cells.
</K2:K10))<>

Thanks! I think this might do it. What if I do have empty cells in the range I choose. Is there a way to have those equations ignore empty cells? Excuse my lack of excel skills.

 

[T. Valko]

Thanks! I think this might do it. What if I do have empty cells in the range I choose. Is there a way to have those equations ignore empty cells? Excuse my lack of excel skills.

Ok, I'm assuming that the date required is typically filled in before the date completed.

On Time:

=SUMPRODUCT(--(I2:I10<>""),--(I2:I10=K2:K10))

Late: no change

Early:

=SUMPRODUCT(--(I2:I10<>""),--(I2:I10 < K2:K10))

 

[joshk87]

Ok, I'm assuming that the date required is typically filled in before the date completed.

On Time:

=SUMPRODUCT(--(I2:I10<>""),--(I2:I10=K2:K10))

Late: no change

Early:

=SUMPRODUCT(--(I2:I10<>""),--(I2:I10 < K2:K10))

I seem to be almost there. It's working, but the result for early should be a 6 in my test spreadsheet and I'm getting a 7. K seems to be counting a blank cell in that equation. Does the <>"" need to be added elsewhere?

 

[T. Valko]

I seem to be almost there. It's working, but the result for early should be a 6 in my test spreadsheet and I'm getting a 7. K seems to be counting a blank cell in that equation. Does the <>"" need to be added elsewhere?

Can you post some sample data and tell us what results you expect?

Which column(s) have empty cells?

If you're filling out a report/form and you predict a date for an item to be completed then the "date required" field should not be empty while the "date completed" field contains an entry.