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)

	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	Note
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	100	Maintenance	35	3/1/2019	12/31/2019	3/15/2019	(1,000.00)	SYMR	10	Discount given due to service complaint
6	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
7	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
8	582	Maintenance	85	4/1/2021	3/31/2022	3/10/2020	4,500.00	SYSR	12	SYSR with 12 months because A NEW CONTRACT was ENTERED separate from the two that was signed and entered at the same time
9	76	Cleaning	123	2/1/2019	1/31/2020	1/15/2019	3,000.00	MYMR	24
10	76	Installation	123	2/1/2019	1/31/2020	2/1/2019	1,000.00	MYMR	24
11	76	Installation	123	2/1/2019	1/31/2020	2/1/2019	(500.00)	MYMR	24	Discount given as there was a delayed installation timing
12	76	Maintenance	123	2/1/2019	1/31/2020	1/15/2019	3,000.00	MYMR	24
13	76	Cleaning	254	2/1/2020	1/31/2021	2/1/2019	2,500.00	MYMR	24
14	76	Installation	254	2/1/2020	1/31/2021	1/15/2019	1,500.00	MYMR	24
15	76	Maintenance	254	2/1/2020	1/31/2021	2/1/2019	3,750.00	MYMR	24

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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).