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!

 

[joshk87]

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.

Both columns will have empty cells. Right now in the small sample im working on the only column with an empty cell is K. Column I is my date required, and K is my completed date. The date completed column will be filled out once we complete a job, it's not a predicted date. Therefore I will have dates in the required, but not all of the completed will have a date right away, only as the job is finished. This list will eventually be well over 100 rows long, so I would like to set it up now as I2:I500 and K2:K500 just to cover myself. That way as i add rows when required I won't have to mess with equation.

I hope this isn't too confusing. I feel like it's 99% there with the help you've given so far. I basically just need the equation to ignore empty cells in both columns.

 

[T. Valko]

Both columns will have empty cells. Right now in the small sample im working on the only column with an empty cell is K. Column I is my date required, and K is my completed date. The date completed column will be filled out once we complete a job, it's not a predicted date. Therefore I will have dates in the required, but not all of the completed will have a date right away, only as the job is finished. This list will eventually be well over 100 rows long, so I would like to set it up now as I2:I500 and K2:K500 just to cover myself. That way as i add rows when required I won't have to mess with equation.

I hope this isn't too confusing. I feel like it's 99% there with the help you've given so far. I basically just need the equation to ignore empty cells in both columns.

OK, here's why I'm confused:

Here's the scenario. I have a column labled "Date Required" (column K) and a column labled "Date Completed" (column I).

The formulas I suggested were based on the above column criteria.

If we can figure out FOR CERTAIN which column is which I'm sure we can get this straightened out!

 

[joshk87]

OK, here's why I'm confused:


The formulas I suggested were based on the above column criteria.

If we can figure out FOR CERTAIN which column is which I'm sure we can get this straightened out!

My fault. Column K is "Completed Date". Column I is "Date Required". Sorry about that.

By the way, I'm leaving the office for the weekend in a few minutes. So I won't be able to do any more testing until Monday morning. Thanks for all the help so far and have a great weekend! I'm sure we'll get this figured out.

 

[T. Valko]

My fault. Column K is "Completed Date". Column I is "Date Required". Sorry about that.

By the way, I'm leaving the office for the weekend in a few minutes. So I won't be able to do any more testing until Monday morning. Thanks for all the help so far and have a great weekend! I'm sure we'll get this figured out.

Try this...

Book1
	I	J	K
1	Required	_	Completed
2	7/16/2005	_	10/17/2001
3	5/2/2007	_	_
4	3/21/2004	_	_
5	2/3/2013	_	2/3/2013
6	9/16/2002	_	7/5/2003
7	1/29/2009	_	6/11/2011
8	8/6/2004	_	8/16/2001
9	7/2/2006	_	_
10	2/10/2008	_	7/30/2001
11	_	_	_
12	_	_	_
13	_	_	_
14	_	_	_
15	_	_	_
16	_	_	_
17	On Time	_	1
18	Late	_	2
19	Early	_	3
Sheet1

On time:

=SUMPRODUCT(--(K2:K15<>""),--(K2:K15=I2:I15))

Late:

=SUMPRODUCT(--(K2:K15>I2:I15))

Early:

=SUMPRODUCT(--(K2:K15<>""),--(K2:K15 < I2:I15))

 

[joshk87]

Works pefect!! Thanks so much for your help! If I wanted to use this formula and reference a different sheet in the workbook would that be easy to do? The sheet with the data on it is called CAD LOG

 

[joshk87]

Works pefect!! Thanks so much for your help! If I wanted to use this formula and reference a different sheet in the workbook would that be easy to do? The sheet with the data on it is called CAD LOG

Nevermind this. Figured it out. Thanks again for the help!!

 

[T. Valko]

Nevermind this. Figured it out. Thanks again for the help!!

Good deal. Thanks for the feedback!