Please see below screenshot.
Column J and I are the ones where I need help with. I have tried "countifs" with multiple scenarios, yet cannot get to work exactly how I picture it.
* Does the customer have one contract with one service type for the entire year? If so, return SYSR (Single Year Single Rev)
* Does the customer have one contract with multiple services for the entire year? If so, return SYMR (Single Year Multi Rev)
* Does the customer have multiple contracts signed at the same time with one service? If so, return MYSR (Multi Year Single Rev)
* Does the customer have multiple contracts signed at the same time with multiple services? If so, return MYMR (Multi Year Multi Rev)
<colgroup><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>
Any thoughts? My brain is too fried! HELP!!!
Column J and I are the ones where I need help with. I have tried "countifs" with multiple scenarios, yet cannot get to work exactly how I picture it.
* Does the customer have one contract with one service type for the entire year? If so, return SYSR (Single Year Single Rev)
* Does the customer have one contract with multiple services for the entire year? If so, return SYMR (Single Year Multi Rev)
* Does the customer have multiple contracts signed at the same time with one service? If so, return MYSR (Multi Year Single Rev)
* Does the customer have multiple contracts signed at the same time with multiple services? If so, return MYMR (Multi Year Multi Rev)
A | B | C | D | E | F | G | H | I | J | |
1 | Customer ID | Service Type | Contract ID | Contract Start Date | Contract End Date | Contract Entered Date | Contract Amount | Type | Revenue Month | |
2 | 1 | Cleaning | 2 | 1/1/2019 | 12/31/2019 | 12/15/2018 | 5,000.00 | SYSR | 12 | |
3 | 100 | Cleaning | 35 | 3/1/2019 | 12/31/2019 | 12/15/2018 | 2,000.00 | SYMR | 10 | |
4 | 100 | Maintenance | 35 | 3/1/2019 | 12/31/2019 | 12/15/2018 | 3,000.00 | SYMR | 10 | |
5 | 582 | Maintenance | 79 | 4/1/2019 | 3/31/2020 | 2/15/2018 | 3,000.00 | MYSR | 24 | *24 because two contracts signed/entered at the same time |
6 | 582 | Maintenance | 80 | 4/1/2020 | 3/31/2021 | 2/15/2018 | 5,000.00 | MYSR | 24 | *24 because two contracts signed/entered at the same time |
7 | 76 | Cleaning | 123 | 2/1/2019 | 1/31/2019 | 1/15/2019 | 3,000.00 | MYMR | 24 | |
8 | 76 | Installation | 123 | 2/1/2019 | 1/31/2019 | 2/1/2019 | 1,000.00 | MYMR | 24 | |
9 | 76 | Maintenance | 123 | 2/1/2019 | 1/31/2019 | 1/15/2019 | 3,000.00 | MYMR | 24 | |
10 | 76 | Cleaning | 254 | 2/1/2020 | 1/31/2021 | 2/1/2019 | 2,500.00 | MYMR | 24 | |
11 | 76 | Installation | 254 | 2/1/2020 | 1/31/2021 | 1/15/2019 | 1,500.00 | MYMR | 24 | |
12 | 76 | Maintenance | 254 | 2/1/2020 | 1/31/2021 | 2/1/2019 | 3,750.00 | MYMR | 24 |
<colgroup><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>
Any thoughts? My brain is too fried! HELP!!!