need to compare dates and count the results

[Heather]

I have two columns of dates. The first column is the date the event
actually occurred or is 0 if nothing has been done yet. The second column
is the target date by which the event should happen, the deadline.

I was able to create conditional formatting for the first column. It turns
each date green if the task was complete or if the due date was more than 24
hours from today. The date turns yellow if it is incomplete and the due
date is 24 hours from today. The date turns red if it's incomplete, and
it's target date is today or has passed.

I would now like to be able to add up how many dates are green, how many are
yellow, and how many are red. Can I count cells based on their format color?
I couldn't figure out a way. So I tried creating a countable column, but I
haven't been able to figure out how to combine the three test conditions
into a single counting function or a single function to create a new row of
data I could count like L1.

Here are my 3 formulas, and what I would like the 3rd column to look like if
I can't count the format color changes in J1 directly.
Green =OR(K2 > Today()+1, AND(K2<>(TODAY()+1),OR(J2 = K2,J2<>0)))
Yellow =AND(K2=TODAY()+1,J2 = 0)
Red =AND(K2<=TODAY(),J2 = 0)


J K
L
Site inspection Site inspection (target)
Compare result
1/12/2007 1/15/2007
G
3/08/2007 3/08/2007
G
0 3/10/2007
Y
0 2/28/2007
R

 

[KC Rippstein]

I am assuming your data starts in row 2 and could potentially go all the way
to row 100. You can adjust the 100 up or down, just do it in all 5 places
in all 6 formulas. You do not need your helper columns J and K if you use
these formulas below.

L2,RED: =SUMPRODUCT((B2:B100<=TODAY())*(A2:A100=0))
L3,YELLOW: =SUMPRODUCT((B2:B100=TODAY()+1)*(A2:A100=0))
L4,GREEN: =COUNT(A2:A100)-L3-L2

 

[KC Rippstein]

Sorry, I have a couple of minor typos below. For some reason, I thought
your data was in columns A and B and you were trying to figure things out
over in J, K, and L. Since your actual data is in J and K, use those
instead of A and B. Very sorry.

Also, when I said to replace the 100 in all 5 places in all 6 formulas, I
meant in all 3 formulas.

What a Monday.