Extensive Excel Formula

J

jeongs1

New Member
Joined
May 23, 2017
Messages
44
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)

ABCDEFGHIJ
1Customer IDService TypeContract IDContract Start DateContract End DateContract Entered DateContract AmountTypeRevenue Month
21Cleaning21/1/201912/31/201912/15/20185,000.00SYSR12
3100Cleaning353/1/201912/31/201912/15/20182,000.00SYMR10
4100Maintenance353/1/201912/31/201912/15/20183,000.00SYMR10
5582Maintenance794/1/20193/31/20202/15/20183,000.00MYSR24*24 because two contracts signed/entered at the same time
6582Maintenance804/1/20203/31/20212/15/20185,000.00MYSR24*24 because two contracts signed/entered at the same time
776Cleaning1232/1/20191/31/20191/15/20193,000.00MYMR24
876Installation1232/1/20191/31/20192/1/20191,000.00MYMR24
976Maintenance1232/1/20191/31/20191/15/20193,000.00MYMR24
1076Cleaning2542/1/20201/31/20212/1/20192,500.00MYMR24
1176Installation2542/1/20201/31/20211/15/20191,500.00MYMR24
1276Maintenance2542/1/20201/31/20212/1/20193,750.00MYMR24

<colgroup><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>


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

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
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.

ABCDEFGHIJK
1Customer IDService TypeContract IDContract Start DateContract End DateContract Entered DateContract AmountHelper 1Helper 2TypeRevenue month
21Cleaning21/1/201912/31/201912/15/2018500011SYSR12
3100Cleaning353/1/201912/31/201912/15/2018200012SYMR10
4100Maintenance353/1/201912/31/201912/15/2018300012SYMR10
5582Maintenance794/1/20193/31/20202/15/2018300022MYSR24
6582Maintenance804/1/20203/31/20212/15/2018500022MYSR24
776Cleaning1232/1/20191/31/20191/15/2019300026MYMR0
876Installation1232/1/20191/31/20192/1/2019100026MYMR0
976Maintenance1232/1/20191/31/20191/15/2019300026MYMR0
1076Cleaning2542/1/20201/31/20212/1/2019250026MYMR12
1176Installation2542/1/20201/31/20211/15/2019150026MYMR12
1276Maintenance2542/1/20201/31/20212/1/2019375026MYMR12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet8​

Worksheet Formulas
CellFormula
J2=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")))
K2=IF(SUMPRODUCT(($A$2:$A$12=A2)*($B$2:$B$12=B2)*($F$2:$F$12=F2))=1,ROUND((E2-D2)/30,0),24)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>


Book1
HI
1Helper 1Helper 2
211
Sheet8
Cell Formulas
RangeFormula
H2=SUMPRODUCT(($B$2:$B$12=B2)*($A$2:$A$12=A2))
I2=SUMPRODUCT(--($A$2:$A$12=A2))
 
Last edited:
Upvote 0
Re: Extensive Excel Formula Help!

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

ABCDEFGHIJ
1Customer IDService TypeContract IDContract Start DateContract End DateContract Entered DateContract AmountTypeRevenue Month
2100Cleaning351/1/201912/31/201912/15/20185,000.00SYSR12
3100Cleaning351/1/201912/31/20191/31/2019-1,000.00SYMR12discount given after the contract was signed due to service complaint
4100Maintenance353/1/201912/31/201912/15/20183,000.00SYMR10
5582Maintenance794/1/20193/31/20202/15/20183,000.00MYSR24*24 because two contracts signed/entered at the same time
6582Maintenance804/1/20203/31/20212/15/20185,000.00MYSR24*24 because two contracts signed/entered at the same time
7582Maintenance854/1/20213/31/20223/10/20204,500.00SYSR12SYSR with 12 months because A NEW CONTRACT was ENTERED separate from the two that was signed and entered at the same time
876Cleaning1232/1/20191/31/20201/15/20193,000.00MYMR24
976Installation1232/1/20191/31/20202/1/20191,000.00MYMR24
1076Maintenance1232/1/20191/31/20201/15/20193,000.00MYMR24
1176Cleaning2542/1/20201/31/20212/1/20192,500.00MYMR24
1276Installation2542/1/20201/31/20211/15/20191,500.00MYMR24
1376Maintenance2542/1/20201/31/20212/1/20193,750.00MYMR24

<tbody>
</tbody>
 
Upvote 0
Re: Extensive Excel Formula Help!

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


ABCDEFGHIJ
1Customer IDService TypeContract IDContract Start DateContract End DateContract Entered DateContract AmountTypeRevenue MonthNote
21Cleaning21/1/201912/31/201912/15/20185,000.00SYSR12
3100Cleaning353/1/201912/31/201912/15/20182,000.00SYMR10
4100Maintenance353/1/201912/31/201912/15/20183,000.00SYMR10
5100Maintenance353/1/201912/31/20193/15/2019(1,000.00)SYMR10Discount given due to service complaint
6582Maintenance794/1/20193/31/20202/15/20183,000.00MYSR2424 because two contracts signed/entered at the same time
7582Maintenance804/1/20203/31/20212/15/20185,000.00MYSR2424 because two contracts signed/entered at the same time
8582Maintenance854/1/20213/31/20223/10/20204,500.00SYSR12SYSR with 12 months because A NEW CONTRACT was ENTERED separate from the two that was signed and entered at the same time
976Cleaning1232/1/20191/31/20201/15/20193,000.00MYMR24
1076Installation1232/1/20191/31/20202/1/20191,000.00MYMR24
1176Installation1232/1/20191/31/20202/1/2019(500.00)MYMR24Discount given as there was a delayed installation timing
1276Maintenance1232/1/20191/31/20201/15/20193,000.00MYMR24
1376Cleaning2542/1/20201/31/20212/1/20192,500.00MYMR24
1476Installation2542/1/20201/31/20211/15/20191,500.00MYMR24
1576Maintenance2542/1/20201/31/20212/1/20193,750.00MYMR24

<tbody>
</tbody>
 
Last edited:
Upvote 0
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
ABCDEFGHIJKLMN
16Customer IDService TypeContract IDContract Start DateContract End DateContract Entered DateContract AmountHelper 1Helper 2TypeRevenue month 1Revenue month 2Expected TypeExpected Revenue Month
17100Cleaning351/1/201912/31/201912/15/2018500012SYMR1224SYSR12
18100Cleaning351/1/201912/31/20191/31/2019-100011SYSR1212SYMR12
19100Maintenance353/1/201912/31/201912/15/2018300012SYMR1024SYMR10
20582Maintenance794/1/20193/31/20202/15/2018300022MYSR2424MYSR24
21582Maintenance804/1/20203/31/20212/15/2018500022MYSR2424MYSR24
22582Maintenance854/1/20213/31/20223/10/2020450011SYSR1212SYSR12
2376Cleaning1232/1/20191/31/20201/15/2019300026MYMR1224MYMR24
2476Installation1232/1/20191/31/20202/1/2019100026MYMR1224MYMR24
2576Maintenance1232/1/20191/31/20201/15/2019300026MYMR1224MYMR24
2676Cleaning2542/1/20201/31/20212/1/2019250026MYMR1224MYMR24
2776Installation2542/1/20201/31/20211/15/2019150026MYMR1224MYMR24
2876Maintenance2542/1/20201/31/20212/1/2019375026MYMR1224MYMR24
Sheet8 (2)
Cell Formulas
RangeFormula
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
ABCDEFGHIJKLMN
32Customer IDService TypeContract IDContract Start DateContract End DateContract Entered DateContract AmountHelper 1Helper 2TypeRevenue month 1Revenue month 2Expected TypeExpected Revenue Month
331Cleaning21/1/201912/31/201912/15/2018500011SYSR1212SYSR12
34100Cleaning353/1/201912/31/201912/15/2018200012SYMR1024SYMR10
35100Maintenance353/1/201912/31/201912/15/2018300012SYMR1024SYMR10
36100Maintenance353/1/201912/31/20193/15/2019-100011SYSR1010SYMR10
37582Maintenance794/1/20193/31/20202/15/2018300022MYSR2424MYSR24
38582Maintenance804/1/20203/31/20212/15/2018500022MYSR2424MYSR24
39582Maintenance854/1/20213/31/20223/10/2020450011SYSR1212SYSR12
4076Cleaning1232/1/20191/31/20201/15/2019300027MYMR1224MYMR24
4176Installation1232/1/20191/31/20202/1/2019100037MYMR2424MYMR24
4276Installation1232/1/20191/31/20202/1/2019-50037MYMR2424MYMR24
4376Maintenance1232/1/20191/31/20201/15/2019300027MYMR1224MYMR24
4476Cleaning2542/1/20201/31/20212/1/2019250027MYMR1224MYMR24
4576Installation2542/1/20201/31/20211/15/2019150037MYMR1224MYMR24
4676Maintenance2542/1/20201/31/20212/1/2019375027MYMR1224MYMR24
Sheet8 (2)
Cell Formulas
RangeFormula
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)
 
Upvote 0
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:

ABCDEFGMNO
1Customer IDService TypeContract IDContract Start DateContract End DateContract Entered DateContract AmountExpected TypeExpected Rev MonthNote
21Cleaning21/1/201912/31/201912/15/20185000SYSR12Customer with one contract with one service type for one year
3100Cleaning353/1/201912/31/201912/15/20182000SYMR10Customer with one contract with multiple type for one year. Rev month 10 (3/2019 - 12/2019)
4100Maintenance353/1/201912/31/201912/15/20183000SYMR10Customer with one contract with multiple type for one year. Rev month 10 (3/2019 - 12/2019)
5100Maintenance353/1/201912/31/20193/15/2019-1000SYMR10Customer with one contract with multiple type for one year. Rev month 10 (3/2019 - 12/2019)
6582Maintenance794/1/20193/31/20202/15/20183000MYSR24Customer with two contracts signed on the same date with one service type - 24 months (4/2019 - 3/2021)
7582Maintenance804/1/20203/31/20212/15/20185000MYSR24Customer with two contracts signed on the same date with one service type - 24 months (4/2019 - 3/2021)
8582Maintenance854/1/20213/31/20223/10/20204500SYSR12Same customer but with a new contract sign/entered date with one service - rev month 12 (4/2021 - 3/2022)
976Cleaning1232/1/20191/31/20201/15/20193000MYMR24Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
1076Installation1232/1/20191/31/20202/1/20191000MYMR24Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
1176Installation1232/1/20191/31/20202/1/2019-500MYMR24Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
1276Maintenance1232/1/20191/31/20201/15/20193000MYMR24Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
1376Cleaning2542/1/20201/31/20212/1/20192500MYMR24Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
1476Installation2542/1/20201/31/20211/15/20191500MYMR24Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
1576Maintenance2542/1/20201/31/20212/1/20193750MYMR24Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)

<tbody>
</tbody>
 
Upvote 0
Re: Extensive Excel Formula Help!

Thanks, give me some time, will try my best to to get back on this.
 
Upvote 0
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
ABCDEFGHIJKL
1Customer IDService TypeContract IDContract Start DateContract End DateContract Entered DateContract AmountTypeRevenue MonthExpected TypeExpected Rev MonthNote
21Cleaning21/1/201912/31/201912/15/20185000SYSR12SYSR12Customer with one contract with one service type for one year
3100Cleaning353/1/201912/31/201912/15/20182000SYMR10SYMR10Customer with one contract with multiple type for one year. Rev month 10 (3/2019 - 12/2019)
4100Maintenance353/1/201912/31/201912/15/20183000SYMR10SYMR10Customer with one contract with multiple type for one year. Rev month 10 (3/2019 - 12/2019)
5100Maintenance353/1/201912/31/20193/15/2019-1000SYMR10SYMR10Customer with one contract with multiple type for one year. Rev month 10 (3/2019 - 12/2019)
6582Maintenance794/1/20193/31/20202/15/20183000MYSR24MYSR24Customer with two contracts signed on the same date with one service type - 24 months (4/2019 - 3/2021)
7582Maintenance804/1/20203/31/20212/15/20185000MYSR24MYSR24Customer with two contracts signed on the same date with one service type - 24 months (4/2019 - 3/2021)
8582Maintenance854/1/20213/31/20223/10/20204500SYSR12SYSR12Same customer but with a new contract sign/entered date with one service - rev month 12 (4/2021 - 3/2022)
976Cleaning1232/1/20191/31/20201/15/20193000MYMR24MYMR24Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
1076Installation1232/1/20191/31/20202/1/20191000MYMR24MYMR24Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
1176Installation1232/1/20191/31/20202/1/2019-500MYMR24MYMR24Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
1276Maintenance1232/1/20191/31/20201/15/20193000MYMR24MYMR24Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
1376Cleaning2542/1/20201/31/20212/1/20192500MYMR24MYMR24Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
1476Installation2542/1/20201/31/20211/15/20191500MYMR24MYMR24Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
1576Maintenance2542/1/20201/31/20212/1/20193750MYMR24MYMR24Customer with multiple contract signed on the same date with multiple service - rev month 24 (2/2019 - 1/2021)
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
15
Views
1K
Wishmaster40
W
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
192
etaf
etaf
F
  • Solved
Formula determine if a date falls between a specific M/D range for multiple years for YTD comparison.
Replies
6
Views
692
flashgordie
F
jski21
  • Solved
Remove Duplicate Lines; Retain Most Recent
Replies
3
Views
263
kevin9999
K
K
  • Solved
Building cell value from multiple cells with Countif?
Replies
7
Views
130
Fluff
Fluff

Forum statistics

Threads
1,214,646
Messages
6,120,715
Members
448,985
Latest member
chocbudda

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top