Extensive Excel Formula

[jeongs1]

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)

[TABLE="width: 1000"]
<colgroup><col><col><col><col><col><col span="2"><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]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] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]Cleaning[/TD]
[TD]2[/TD]
[TD]1/1/2019[/TD]
[TD]12/31/2019[/TD]
[TD]12/15/2018[/TD]
[TD]5,000.00[/TD]
[TD]SYSR[/TD]
[TD="align: right"]12[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]100[/TD]
[TD]Cleaning[/TD]
[TD]35[/TD]
[TD]3/1/2019[/TD]
[TD]12/31/2019[/TD]
[TD]12/15/2018[/TD]
[TD]2,000.00[/TD]
[TD]SYMR[/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]100[/TD]
[TD]Maintenance[/TD]
[TD]35[/TD]
[TD]3/1/2019[/TD]
[TD]12/31/2019[/TD]
[TD]12/15/2018[/TD]
[TD]3,000.00[/TD]
[TD]SYMR[/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]582[/TD]
[TD]Maintenance[/TD]
[TD]79[/TD]
[TD]4/1/2019[/TD]
[TD]3/31/2020[/TD]
[TD]2/15/2018[/TD]
[TD]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]6[/TD]
[TD]582[/TD]
[TD]Maintenance[/TD]
[TD]80[/TD]
[TD]4/1/2020[/TD]
[TD]3/31/2021[/TD]
[TD]2/15/2018[/TD]
[TD]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]7[/TD]
[TD]76[/TD]
[TD]Cleaning[/TD]
[TD]123[/TD]
[TD]2/1/2019[/TD]
[TD]1/31/2019[/TD]
[TD]1/15/2019[/TD]
[TD]3,000.00[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]76[/TD]
[TD]Installation[/TD]
[TD]123[/TD]
[TD]2/1/2019[/TD]
[TD]1/31/2019[/TD]
[TD]2/1/2019[/TD]
[TD]1,000.00[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]76[/TD]
[TD]Maintenance[/TD]
[TD]123[/TD]
[TD]2/1/2019[/TD]
[TD]1/31/2019[/TD]
[TD]1/15/2019[/TD]
[TD]3,000.00[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]76[/TD]
[TD]Cleaning[/TD]
[TD]254[/TD]
[TD]2/1/2020[/TD]
[TD]1/31/2021[/TD]
[TD]2/1/2019[/TD]
[TD]2,500.00[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]76[/TD]
[TD]Installation[/TD]
[TD]254[/TD]
[TD]2/1/2020[/TD]
[TD]1/31/2021[/TD]
[TD]1/15/2019[/TD]
[TD]1,500.00[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]76[/TD]
[TD]Maintenance[/TD]
[TD]254[/TD]
[TD]2/1/2020[/TD]
[TD]1/31/2021[/TD]
[TD]2/1/2019[/TD]
[TD]3,750.00[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any thoughts? My brain is too fried! HELP!!!

 

[Aryatect]

Re: Extensive Excel Formula Help!

Hi jeongs1,

I think with the help of 2 helper columns (which can be used in the formula in column J of the below data if required) we might get what you are looking for. For revenue month I have attempted based on what I could understand looking at the data. Let me know if that does not work for you.

	A	B	C	D	E	F	G	H	I	J	K
Customer ID	Service Type	Contract ID	Contract Start Date	Contract End Date	Contract Entered Date	Contract Amount	Helper 1	Helper 2	Type	Revenue month
Cleaning	SYSR
Cleaning	SYMR
Maintenance	SYMR
Maintenance	MYSR
Maintenance	MYSR
Cleaning	MYMR
Installation	MYMR
Maintenance	MYMR
Cleaning	MYMR
Installation	MYMR
Maintenance	MYMR

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/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"]5000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]100[/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"]2000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]100[/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"]3000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]582[/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"]3000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]24[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]582[/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"]5000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]24[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]76[/TD]

[TD="align: right"]123[/TD]
[TD="align: right"]2/1/2019[/TD]
[TD="align: right"]1/31/2019[/TD]
[TD="align: right"]1/15/2019[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]76[/TD]

[TD="align: right"]123[/TD]
[TD="align: right"]2/1/2019[/TD]
[TD="align: right"]1/31/2019[/TD]
[TD="align: right"]2/1/2019[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]76[/TD]

[TD="align: right"]123[/TD]
[TD="align: right"]2/1/2019[/TD]
[TD="align: right"]1/31/2019[/TD]
[TD="align: right"]1/15/2019[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]76[/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"]2500[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]76[/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"]1500[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]76[/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"]3750[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]12[/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=IF(AND(H2=1,I2=1),"SYSR",IF(AND(H2=1,I2>1),"SYMR",IF(AND(H2>1,I2>1),IF(H2=I2,"MYSR","MYMR"),"CHECK")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]=IF(SUMPRODUCT(($A$2:$A$12=A2)*($B$2:$B$12=B2)*($F$2:$F$12=F2))=1,ROUND((E2-D2)/30,0),24)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Book1
	H	I
1	Helper 1	Helper 2
2	1	1
Sheet8

Cell Formulas
Range	Formula
H2	=SUMPRODUCT(($B$2:$B$12=B2)*($A$2:$A$12=A2))
I2	=SUMPRODUCT(--($A$2:$A$12=A2))

 

[jeongs1]

Re: Extensive Excel Formula Help!

What if the data were to look like below? I added two new rows in red below:

[TABLE="width: 1300"]
<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][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]100[/TD]
[TD]Cleaning[/TD]
[TD="align: right"]35[/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]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"]1/1/2019[/TD]
[TD="align: right"]12/31/2019[/TD]
[TD="align: right"]1/31/2019[/TD]
[TD="align: right"]-1,000.00[/TD]
[TD]SYMR[/TD]
[TD]12[/TD]
[TD]discount given after the contract was signed due to service complaint[/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]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/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]24[/TD]
[TD]*24 because two contracts signed/entered at the same time[/TD]
[/TR]
[TR]
[TD="align: right"]6[/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]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"]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]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"]8[/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]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/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]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/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]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/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]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/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]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/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]24[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jeongs1]

Re: Extensive Excel Formula Help!

Aryatect
What if I were to have below additional scenarios in red?


[TABLE="class: cms_table_outer_border, width: 1310, align: left"]
<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]
[/TR]
</tbody>[/TABLE]

 

[Aryatect]

Re: Extensive Excel Formula Help!

Hi,

So somewhere the logic is not matching, I am posting your both updated tables and formula it used for both, I have 2 Revenue month in this having 2 different logic, can you evaluate the condition of this or can you more clearly define on what all columns and values each Type and Revenue month is dependent on.

Data with 2 red lines inserted:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
16	Customer ID	Service Type	Contract ID	Contract Start Date	Contract End Date	Contract Entered Date	Contract Amount	Helper 1	Helper 2	Type	Revenue month 1	Revenue month 2	Expected Type	Expected Revenue Month
17	100	Cleaning	35	1/1/2019	12/31/2019	12/15/2018	5000	1	2	SYMR	12	24	SYSR	12
18	100	Cleaning	35	1/1/2019	12/31/2019	1/31/2019	-1000	1	1	SYSR	12	12	SYMR	12
19	100	Maintenance	35	3/1/2019	12/31/2019	12/15/2018	3000	1	2	SYMR	10	24	SYMR	10
20	582	Maintenance	79	4/1/2019	3/31/2020	2/15/2018	3000	2	2	MYSR	24	24	MYSR	24
21	582	Maintenance	80	4/1/2020	3/31/2021	2/15/2018	5000	2	2	MYSR	24	24	MYSR	24
22	582	Maintenance	85	4/1/2021	3/31/2022	3/10/2020	4500	1	1	SYSR	12	12	SYSR	12
23	76	Cleaning	123	2/1/2019	1/31/2020	1/15/2019	3000	2	6	MYMR	12	24	MYMR	24
24	76	Installation	123	2/1/2019	1/31/2020	2/1/2019	1000	2	6	MYMR	12	24	MYMR	24
25	76	Maintenance	123	2/1/2019	1/31/2020	1/15/2019	3000	2	6	MYMR	12	24	MYMR	24
26	76	Cleaning	254	2/1/2020	1/31/2021	2/1/2019	2500	2	6	MYMR	12	24	MYMR	24
27	76	Installation	254	2/1/2020	1/31/2021	1/15/2019	1500	2	6	MYMR	12	24	MYMR	24
28	76	Maintenance	254	2/1/2020	1/31/2021	2/1/2019	3750	2	6	MYMR	12	24	MYMR	24
Sheet8 (2)

Cell Formulas
Range	Formula
H17	=SUMPRODUCT(($B$17:$B$28=B17)*($A$17:$A$28=A17)*(YEAR($F$17:$F$28)=YEAR(F17)))
I17	=SUMPRODUCT(($A$17:$A$28=A17)*(YEAR($F$17:$F$28)=YEAR(F17)))
J17	=IF(AND(H17=1,I17=1),"SYSR",IF(AND(H17=1,I17>1),"SYMR",IF(AND(H17>1,I17>1),IF(H17=I17,"MYSR","MYMR"),"CHECK")))
K17	=IF(SUMPRODUCT(($A$17:$A$28=A17)*($F$17:$F$28=F17)*($B$17:$B$28=B17))=1,ROUND((E17-D17)/30,0),24)
L17	=IF(SUMPRODUCT(($A$17:$A$28=A17)*($F$17:$F$28=F17))=1,ROUND((E17-D17)/30,0),24)

Data with 3 red entries inserted:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
32	Customer ID	Service Type	Contract ID	Contract Start Date	Contract End Date	Contract Entered Date	Contract Amount	Helper 1	Helper 2	Type	Revenue month 1	Revenue month 2	Expected Type	Expected Revenue Month
33	1	Cleaning	2	1/1/2019	12/31/2019	12/15/2018	5000	1	1	SYSR	12	12	SYSR	12
34	100	Cleaning	35	3/1/2019	12/31/2019	12/15/2018	2000	1	2	SYMR	10	24	SYMR	10
35	100	Maintenance	35	3/1/2019	12/31/2019	12/15/2018	3000	1	2	SYMR	10	24	SYMR	10
36	100	Maintenance	35	3/1/2019	12/31/2019	3/15/2019	-1000	1	1	SYSR	10	10	SYMR	10
37	582	Maintenance	79	4/1/2019	3/31/2020	2/15/2018	3000	2	2	MYSR	24	24	MYSR	24
38	582	Maintenance	80	4/1/2020	3/31/2021	2/15/2018	5000	2	2	MYSR	24	24	MYSR	24
39	582	Maintenance	85	4/1/2021	3/31/2022	3/10/2020	4500	1	1	SYSR	12	12	SYSR	12
40	76	Cleaning	123	2/1/2019	1/31/2020	1/15/2019	3000	2	7	MYMR	12	24	MYMR	24
41	76	Installation	123	2/1/2019	1/31/2020	2/1/2019	1000	3	7	MYMR	24	24	MYMR	24
42	76	Installation	123	2/1/2019	1/31/2020	2/1/2019	-500	3	7	MYMR	24	24	MYMR	24
43	76	Maintenance	123	2/1/2019	1/31/2020	1/15/2019	3000	2	7	MYMR	12	24	MYMR	24
44	76	Cleaning	254	2/1/2020	1/31/2021	2/1/2019	2500	2	7	MYMR	12	24	MYMR	24
45	76	Installation	254	2/1/2020	1/31/2021	1/15/2019	1500	3	7	MYMR	12	24	MYMR	24
46	76	Maintenance	254	2/1/2020	1/31/2021	2/1/2019	3750	2	7	MYMR	12	24	MYMR	24
Sheet8 (2)

Cell Formulas
Range	Formula
H33	=SUMPRODUCT(($B$33:$B$46=B33)*($A$33:$A$46=A33)*(YEAR($F$33:$F$46)=YEAR(F33)))
I33	=SUMPRODUCT(($A$33:$A$46=A33)*(YEAR($F$33:$F$46)=YEAR(F33)))
J33	=IF(AND(H33=1,I33=1),"SYSR",IF(AND(H33=1,I33>1),"SYMR",IF(AND(H33>1,I33>1),IF(H33=I33,"MYSR","MYMR"),"CHECK")))
K33	=IF(SUMPRODUCT(($A$33:$A$46=A33)*($F$33:$F$46=F33)*($B$33:$B$46=B33))=1,ROUND((E33-D33)/30,0),24)
L33	=IF(SUMPRODUCT(($A$33:$A$46=A33)*($F$33:$F$46=F33))=1,ROUND((E33-D33)/30,0),24)

 

[jeongs1]

Re: Extensive Excel Formula Help!

Hi Aryatect, Thank you for you help on this.

Please see below table with finalized data, my logic, and the result that I am looking for:

[TABLE="class: grid, width: 1632"]
<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]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]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]Expected Type[/TD]
[TD]Expected Rev Month[/TD]
[TD]Note[/TD]
[/TR]
[TR]
[TD]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"]5000[/TD]
[TD]SYSR[/TD]
[TD="align: right"]12[/TD]
[TD]Customer with one contract with one service type for one year[/TD]
[/TR]
[TR]
[TD]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"]2000[/TD]
[TD]SYMR[/TD]
[TD="align: right"]10[/TD]
[TD]Customer with one contract with multiple type for one year. Rev month 10 (3/2019 - 12/2019)[/TD]
[/TR]
[TR]
[TD]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"]3000[/TD]
[TD]SYMR[/TD]
[TD="align: right"]10[/TD]
[TD]Customer with one contract with multiple type for one year. Rev month 10 (3/2019 - 12/2019)[/TD]
[/TR]
[TR]
[TD]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"]-1000[/TD]
[TD]SYMR[/TD]
[TD="align: right"]10[/TD]
[TD]Customer with one contract with multiple type for one year. Rev month 10 (3/2019 - 12/2019)[/TD]
[/TR]
[TR]
[TD]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"]3000[/TD]
[TD]MYSR[/TD]
[TD="align: right"]24[/TD]
[TD]Customer with two contracts signed on the same date with one service type - 24 months (4/2019 - 3/2021)[/TD]
[/TR]
[TR]
[TD]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"]5000[/TD]
[TD]MYSR[/TD]
[TD="align: right"]24[/TD]
[TD]Customer with two contracts signed on the same date with one service type - 24 months (4/2019 - 3/2021)[/TD]
[/TR]
[TR]
[TD]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"]4500[/TD]
[TD]SYSR[/TD]
[TD="align: right"]12[/TD]
[TD]Same customer but with a new contract sign/entered date with one service - rev month 12 (4/2021 - 3/2022)[/TD]
[/TR]
[TR]
[TD]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"]3000[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD]Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)[/TD]
[/TR]
[TR]
[TD]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"]1000[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD]Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)[/TD]
[/TR]
[TR]
[TD]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[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD]Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)[/TD]
[/TR]
[TR]
[TD]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"]3000[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD]Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)[/TD]
[/TR]
[TR]
[TD]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"]2500[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD]Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)[/TD]
[/TR]
[TR]
[TD]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"]1500[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD]Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)[/TD]
[/TR]
[TR]
[TD]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"]3750[/TD]
[TD]MYMR[/TD]
[TD="align: right"]24[/TD]
[TD]Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aryatect]

Re: Extensive Excel Formula Help!

Thanks, give me some time, will try my best to to get back on this.

 

[Aryatect]

Re: Extensive Excel Formula Help!

Hi jeongs1,

This should do the trick! Used a great trick from Mike "excelisfun" Girvin's video to calculate unique entries.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Customer ID	Service Type	Contract ID	Contract Start Date	Contract End Date	Contract Entered Date	Contract Amount	Type	Revenue Month	Expected Type	Expected Rev Month	Note
2	1	Cleaning	2	1/1/2019	12/31/2019	12/15/2018	5000	SYSR	12	SYSR	12	Customer with one contract with one service type for one year
3	100	Cleaning	35	3/1/2019	12/31/2019	12/15/2018	2000	SYMR	10	SYMR	10	Customer with one contract with multiple type for one year. Rev month 10 (3/2019 - 12/2019)
4	100	Maintenance	35	3/1/2019	12/31/2019	12/15/2018	3000	SYMR	10	SYMR	10	Customer with one contract with multiple type for one year. Rev month 10 (3/2019 - 12/2019)
5	100	Maintenance	35	3/1/2019	12/31/2019	3/15/2019	-1000	SYMR	10	SYMR	10	Customer with one contract with multiple type for one year. Rev month 10 (3/2019 - 12/2019)
6	582	Maintenance	79	4/1/2019	3/31/2020	2/15/2018	3000	MYSR	24	MYSR	24	Customer with two contracts signed on the same date with one service type - 24 months (4/2019 - 3/2021)
7	582	Maintenance	80	4/1/2020	3/31/2021	2/15/2018	5000	MYSR	24	MYSR	24	Customer with two contracts signed on the same date with one service type - 24 months (4/2019 - 3/2021)
8	582	Maintenance	85	4/1/2021	3/31/2022	3/10/2020	4500	SYSR	12	SYSR	12	Same customer but with a new contract sign/entered date with one service - rev month 12 (4/2021 - 3/2022)
9	76	Cleaning	123	2/1/2019	1/31/2020	1/15/2019	3000	MYMR	24	MYMR	24	Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
10	76	Installation	123	2/1/2019	1/31/2020	2/1/2019	1000	MYMR	24	MYMR	24	Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
11	76	Installation	123	2/1/2019	1/31/2020	2/1/2019	-500	MYMR	24	MYMR	24	Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
12	76	Maintenance	123	2/1/2019	1/31/2020	1/15/2019	3000	MYMR	24	MYMR	24	Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
13	76	Cleaning	254	2/1/2020	1/31/2021	2/1/2019	2500	MYMR	24	MYMR	24	Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
14	76	Installation	254	2/1/2020	1/31/2021	1/15/2019	1500	MYMR	24	MYMR	24	Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
15	76	Maintenance	254	2/1/2020	1/31/2021	2/1/2019	3750	MYMR	24	MYMR	24	Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
Sheet2

Cell Formulas
Range	Formula
H2	{=IF(SUM(IF(FREQUENCY(IF(($A$2:$A$15=A2)*($F$2:$F$15=F2),MATCH($C$2:$C$15,$C$2:$C$15,0)),ROW($A$2:$A$15)-ROW($A$1)),1))=1,"SY","MY")&IF(SUM(IF(FREQUENCY(IF($A$2:$A$15=A2,MATCH($B$2:$B$15,$B$2:$B$15,0)),ROW($A$2:$A$15)-ROW($A$1)),1))=1,"SR","MR")}
I2	{=ROUND((AGGREGATE(14,6,IF(($A$2:$A$15=A2)*($B$2:$B$15=B2)*(YEAR($F$2:$F$15)=YEAR(F2)),$E$2:$E$15),1)-AGGREGATE(15,6,IF(($A$2:$A$15=A2)*($B$2:$B$15=B2)*(YEAR($F$2:$F$15)=YEAR(F2)),$D$2:$D$15),1))/30,0)}
Press CTRL+SHIFT+ENTER to enter array formulas.