Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
=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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
totaling months | Excel Discussion (Misc queries) | |||
How do i change 15 months to read 1 year and 3 months? | Excel Discussion (Misc queries) | |||
How do I calculate total of months that have passed? | Excel Discussion (Misc queries) | |||
Converting months to years | Excel Worksheet Functions | |||
How do I display months and years between two dates | Excel Discussion (Misc queries) |