Hi there - this is my first post here, though I've been greatly appreciative of the tons of help you've given others in the past that also was a big help to me!
So,I have a table like the one below that's 150k lines of sales contract info - each line item is a one-year contract. I'm trying to figure out the average contract length for each product so I have to get accurate lengths for each contract. The problem is if I try to use COUNTIF for a given customer and product it doesn't work.
As an example below, with Nashop, COUNTIF gives me "7" indicating Nashop has a contract that's lasted 7 years. But there are actually two instances of the same contract at Nashop, one contract started 4/1/12 and went through 3/31/16 (highlighted in yellow), thus 4 years; the other contract started 5/1/12 and ended 4/30/15 (highlighted in blue), thus 3 years.
The table is organized by customer and product number with start and end dates for each line item (which is a one-year contract period). I just need to be able to count the rows representing different contracts even though the product is the same - and the key to recognizing the same contract is the common annual start dates for contracts that are simply renewed.
Can anyone suggest a macro or array formula or anything that can recognize and count the lines for each different contract? Something that could be applied to the entire table - maybe in a new column? I'd like to do some kind of COUNTIF that would give me a result for Nashop of 4 and 3 rather than 7?
Any advice will be very much appreciated. Thanks - Mike
So,I have a table like the one below that's 150k lines of sales contract info - each line item is a one-year contract. I'm trying to figure out the average contract length for each product so I have to get accurate lengths for each contract. The problem is if I try to use COUNTIF for a given customer and product it doesn't work.
As an example below, with Nashop, COUNTIF gives me "7" indicating Nashop has a contract that's lasted 7 years. But there are actually two instances of the same contract at Nashop, one contract started 4/1/12 and went through 3/31/16 (highlighted in yellow), thus 4 years; the other contract started 5/1/12 and ended 4/30/15 (highlighted in blue), thus 3 years.
| Customer Name | Location | Product Number | Start Date | End Date |
| GPG | Nashop | CVB-23 | 04/01/12 | 03/31/13 |
| GPG | Nashop | CVB-23 | 04/01/15 | 03/31/16 |
| GPG | Nashop | CVB-23 | 04/01/13 | 03/31/14 |
| GPG | Nashop | CVB-23 | 04/01/14 | 03/31/15 |
| GPG | Nashop | CVB-23 | 05/01/12 | 04/30/13 |
| GPG | Nashop | CVB-23 | 05/01/14 | 04/30/15 |
| GPG | Nashop | CVB-23 | 05/01/13 | 04/30/14 |
| GPG | Wilmot | CVB-23 | 12/01/12 | 11/30/13 |
| GPG | Wilmot | CVB-23 | 12/01/13 | 11/30/14 |
| GPG | Wilmot | CVB-23 | 03/15/13 | 3/14/14 |
| GPG | Wilmot | CVB-23 | 3/15/14 | 3/14/15 |
| GPG | Wilmot | CVB-23 | 3/15/15 | 3/15/16 |
| GPG | Wilmot | CVB-23 | 09/15/12 | 12/14/12 |
| GPG | Wilmot | CVB-23 | 12/15/11 | 03/14/12 |
The table is organized by customer and product number with start and end dates for each line item (which is a one-year contract period). I just need to be able to count the rows representing different contracts even though the product is the same - and the key to recognizing the same contract is the common annual start dates for contracts that are simply renewed.
Can anyone suggest a macro or array formula or anything that can recognize and count the lines for each different contract? Something that could be applied to the entire table - maybe in a new column? I'd like to do some kind of COUNTIF that would give me a result for Nashop of 4 and 3 rather than 7?
Any advice will be very much appreciated. Thanks - Mike
