Countifs/sumproduct help!!!

[jeongs1]

Please see below:
Column J and I are the ones where I need help with:
* 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)

[TABLE="class: outer_border, width: 1310, align: left"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Customer ID[/TD]
[TD]Service Type[/TD]
[TD]Contract ID[/TD]
[TD]Contract Start Date[/TD]
[TD]Contract End Date[/TD]
[TD]Contract Entered Date[/TD]
[TD]Contract Amount[/TD]
[TD]Type[/TD]
[TD]Revenue Month[/TD]
[TD]Note[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Cleaning[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]12/31/2019[/TD]
[TD="align: right"]12/15/2018[/TD]
[TD="align: right"]5,000.00[/TD]
[TD]SYSR[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]100[/TD]
[TD]Cleaning[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]12/31/2019[/TD]
[TD="align: right"]12/15/2018[/TD]
[TD="align: right"]2,000.00[/TD]
[TD]SYMR[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]100[/TD]
[TD]Maintenance[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]12/31/2019[/TD]
[TD="align: right"]12/15/2018[/TD]
[TD="align: right"]3,000.00[/TD]
[TD]SYMR[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]100[/TD]
[TD]Maintenance[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]12/31/2019[/TD]
[TD="align: right"]3/15/2019[/TD]
[TD="align: right"](1,000.00)[/TD]
[TD]SYMR[/TD]
[TD="align: right"]10[/TD]
[TD]Discount given due to service complaint[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]582[/TD]
[TD]Maintenance[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]4/1/2019[/TD]
[TD="align: right"]3/31/2020[/TD]
[TD="align: right"]2/15/2018[/TD]
[TD="align: right"]3,000.00[/TD]
[TD]MYSR[/TD]
[TD="align: right"]24[/TD]
[TD]24 because two contracts signed/entered at the same time[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]582[/TD]
[TD]Maintenance[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]4/1/2020[/TD]
[TD="align: right"]3/31/2021[/TD]
[TD="align: right"]2/15/2018[/TD]
[TD="align: right"]5,000.00[/TD]
[TD]MYSR[/TD]
[TD="align: right"]24[/TD]
[TD]24 because two contracts signed/entered at the same time[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]582[/TD]
[TD]Maintenance[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]4/1/2021[/TD]
[TD="align: right"]3/31/2022[/TD]
[TD="align: right"]3/10/2020[/TD]
[TD="align: right"]4,500.00[/TD]
[TD]SYSR[/TD]
[TD="align: right"]12[/TD]
[TD]SYSR with 12 months because A NEW CONTRACT was ENTERED separate from the two that was signed and entered at the same time[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]76[/TD]
[TD]Cleaning[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]2/1/2019[/TD]
[TD="align: right"]1/31/2020[/TD]
[TD="align: right"]1/15/2019[/TD]
[TD="align: right"]3,000.00[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]76[/TD]
[TD]Installation[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]2/1/2019[/TD]
[TD="align: right"]1/31/2020[/TD]
[TD="align: right"]2/1/2019[/TD]
[TD="align: right"]1,000.00[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]76[/TD]
[TD]Installation[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]2/1/2019[/TD]
[TD="align: right"]1/31/2020[/TD]
[TD="align: right"]2/1/2019[/TD]
[TD="align: right"](500.00)[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD]Discount given as there was a delayed installation timing[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]76[/TD]
[TD]Maintenance[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]2/1/2019[/TD]
[TD="align: right"]1/31/2020[/TD]
[TD="align: right"]1/15/2019[/TD]
[TD="align: right"]3,000.00[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]76[/TD]
[TD]Cleaning[/TD]
[TD="align: right"]254[/TD]
[TD="align: right"]2/1/2020[/TD]
[TD="align: right"]1/31/2021[/TD]
[TD="align: right"]2/1/2019[/TD]
[TD="align: right"]2,500.00[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]76[/TD]
[TD]Installation[/TD]
[TD="align: right"]254[/TD]
[TD="align: right"]2/1/2020[/TD]
[TD="align: right"]1/31/2021[/TD]
[TD="align: right"]1/15/2019[/TD]
[TD="align: right"]1,500.00[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]76[/TD]
[TD]Maintenance[/TD]
[TD="align: right"]254[/TD]
[TD="align: right"]2/1/2020[/TD]
[TD="align: right"]1/31/2021[/TD]
[TD="align: right"]2/1/2019[/TD]
[TD="align: right"]3,750.00[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel-questions/1102301-extensive-excel-formula.html#post5300999

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).