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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>