Totaling Months
 

I have 3 columns in my worksheet, column F Date Due, column G is Date
Completed, and column H is Status (completed on time (GREEN) and late (RED)).
What I am looking for is to get a subtotal of how many line items were
closed on time and late. For example in the completed column there could be
10 lines completed in Jan. of those 8 were closed on or before the due date
and 2 were completed late. This would be true for all of the other months.
The columns look like this:


Date Due Date Complete Status
R/G

2/1/2005 2/1/2005 GREEN
1/1/2005 2/1/2005 RED
2/1/2005 3/1/2005 RED
4/1/2005 4/1/2005 GREEN
4/30/2005 5/1/2005 RED
5/1/2005 5/2/2005 RED
6/1/2005 5/3/2005 GREEN
5/1/2005 6/1/2005 RED
7/1/2005 7/1/2005 GREEN
8/1/2005 8/1/2005 GREEN
10/1/2005 9/1/2005 GREEN
9/1/2005 9/1/2005 GREEN
12/1/2005 11/1/2005 GREEN
11/1/2005 12/1/2005 RED

Basically what I need is to find how many line items were closed on time or
late for a given months.

=SUMPRODUCT(--(TEXT(A1:A100,"mmm")="Jan"),--(C1:C100="RED"))

for late, just change to GREEN for on time.

--
HTH

Bob Phillips

"Roy" wrote in message
...
I have 3 columns in my worksheet, column F Date Due, column G is Date
Completed, and column H is Status (completed on time (GREEN) and late
(RED)).
What I am looking for is to get a subtotal of how many line items were
closed on time and late. For example in the completed column there could
be
10 lines completed in Jan. of those 8 were closed on or before the due
date
and 2 were completed late. This would be true for all of the other
months.
The columns look like this:


Date Due Date Complete Status
R/G

2/1/2005 2/1/2005 GREEN
1/1/2005 2/1/2005 RED
2/1/2005 3/1/2005 RED
4/1/2005 4/1/2005 GREEN
4/30/2005 5/1/2005 RED
5/1/2005 5/2/2005 RED
6/1/2005 5/3/2005 GREEN
5/1/2005 6/1/2005 RED
7/1/2005 7/1/2005 GREEN
8/1/2005 8/1/2005 GREEN
10/1/2005 9/1/2005 GREEN
9/1/2005 9/1/2005 GREEN
12/1/2005 11/1/2005 GREEN
11/1/2005 12/1/2005 RED

Basically what I need is to find how many line items were closed on time
or
late for a given months.