# Need a formula to calculate Monthly amount if every couple of months Amt is increased

#### Kiriko

##### New Member
Hopefully this is as easy as the title makes it seem:

I have the below data:

 Month Month Number Total Cost Mar-19 3.00 71,250.00 Mar-19 3.00 71,250.00 Mar-19 3.00 41,250.00 Mar-19 3.00 41,250.00 Jun-19 6.00 16,988.00 Mar-19 3.00 13,904.21 Oct-19 10.00 3,089.82 Aug-19 8.00 7,506.33 Mar-19 3.00 78,750.00 Mar-19 3.00 78,750.00 Mar-19 3.00 30,000.00 Mar-19 3.00 30,000.00 Oct-19 10.00 8,269.17 Oct-19 10.00 8,269.17 Jun-19 6.00 16,433.69 Jun-19 6.00 35,444.25 Jun-19 6.00 16,210.33 Jun-19 6.00 26,746.78 Mar-19 3.00 76,301.25 Mar-19 3.00 71,250.00 Mar-19 3.00 53,166.38 Mar-19 3.00 40,120.16 Mar-19 3.00 24,650.54 Mar-19 3.00 24,650.54 Total 885,500.59

<tbody>
</tbody>
 Starting Month Month Number Total Cost to start in: NETWORKING DAYS March 3 746,543.06 218 June 6 111,823.04 152 August 8 7,506.33 109 October 10 19,628.16 66 Total 885,500.59

<tbody>
</tbody>

I need to do the below calculations:

 Calculation: Month NetWorking Days_19 Monthly Cost Monthly Cost Monthly Cost Monthly Cost Total Montly Cost 2019 Start in Month Mar-19 Jun-19 Aug-19 Oct-19 Total Cost for the Year 746,543.06 111,823.04 7,506.33 19,628.16 NET WORKING DAYS 261 218 152.00 109.00 66.00 0 Jan-19 23 - 0 Feb-19 20 - (746/218)*NWD in Month Mar-19 21 71,914.70 71,914.70 (746/218)*NWD in Month Apr-19 22 75,339.21 75,339.21 (746/218)*NWD in Month May-19 23 78,763.72 78,763.72 (746/218)+(111/152)*NWD in Month Jun-19 20 68,490.19 14,713.56 83,203.75 (746/218)+(111/152)*NWD in Month Jul-19 23 78,763.72 16,920.59 95,684.31 (746/218)+(111/152)+(7/109)*NWD in Month Aug-19 22 75,339.21 16,184.91 1,515.04 93,039.16 (746/218)+(111/152)+(7/109)*NWD in Month Sep-19 21 71,914.70 15,449.24 1,446.17 88,810.11 (746/218)+(111/152)+(7/109)+(19/66)*NWD in Month Oct-19 23 78,763.72 16,920.59 1,583.91 6,840.12 104,108.33 (746/218)+(111/152)+(7/109)+(19/66)*NWD in Month Nov-19 21 71,914.70 15,449.24 1,446.17 6,245.32 95,055.43 (746/218)+(111/152)+(7/109)+(19/66)*NWD in Month Dec-19 22 75,339.21 16,184.91 1,515.04 6,542.72 99,581.88 746,543.06 111,823.04 7,506.33 19,628.16 885,500.59

<tbody>
</tbody>
To get the result in the below format:

 Month 2019 Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 NETWORKING DAYS 23 20 21 22 23 20 23 22 21 23 21 22 Check Total Cost - - 71,914.70 75,339.21 78,763.72 83,203.75 95,684.31 93,039.16 88,810.11 104,108.33 95,055.43 99,581.88 885,500.59 Thank you to anyone who can and tries to help

<tbody>
</tbody>

Last edited:

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi Kiriko,

Maybe you could use this, note that my figures reflect only the first table of data, but it looks like it achieves the right format?

Copy the formulas across your table, note that the SUM(\$B\$29:B\$29) formula expands to add each Months Costs as it goes across. Also the Dates in the Months row are the 1st of each month just formatted as "mmm-yy".

You could also 2019 Public Holidays in the [Holidays]criteria in =NETWORKDAYS(B\$30,EOMONTH(B\$30,0),[holidays]) as a range.

Book1
ABC
1MonthMonth NumberTotal Cost
2Mar-19371,250.00
3Mar-19371,250.00
4Mar-19341,250.00
5Mar-19341,250.00
6Jun-19616,988.00
7Mar-19313,904.21
8Oct-19103,089.82
9Aug-1987,506.33
10Mar-19378,750.00
11Mar-19378,750.00
12Mar-19330,000.00
13Mar-19330,000.00
14Oct-19108,269.17
15Oct-19108,269.17
16Jun-19616,433.69
17Jun-19635,444.25
18Jun-19616,210.33
19Jun-19626,746.78
20Mar-19376,301.25
21Mar-19371,250.00
22Mar-19353,166.38
23Mar-19340,120.16
24Mar-19324,650.54
25Mar-19324,650.54
Sheet1

Book1
ABCDEFGHIJKLMN
27NETWORK MONTH232021222320232221232122
28NETWORK YR26123821819717515213210987664322
29Monthly Costs 71,914.7014,713.561,515.046,840.12
30Months01-01-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19Check
31Total Costs 71,914.7071,914.7071,914.7086,628.2686,628.2688,143.3088,143.3094,983.4294,983.4294,983.42850,237.46
Sheet1
Cell Formulas
RangeFormula
B27=NETWORKDAYS(B\$30,EOMONTH(B\$30,0))
B28=NETWORKDAYS(B\$30,EOMONTH(\$M\$30,0))
B29=B\$27*(SUMIFS(\$C\$2:\$C\$25,\$A\$2:\$A\$25,">="&B\$30,\$A\$2:\$A\$25,"<="&EOMONTH(B\$30,0))/B\$28)
B31=SUM(\$B\$29:B\$29)
C30=EOMONTH(B30,0)+1
N31=SUM(\$B\$31:\$M\$31)

Thanks RasGhul, your suggestion was easy to follow, however, I still need to calculate by the Networking Days per month. Is there a way to get excel to recognize when there is change to the monthly cost and then do the divisions by Network yr and add that to the original monthly total amount.....

Ok so my formula is doing the following for the March figure;

NetworkDays(MARCH(21))*SUMIFS(March Total Cost(746,543.08))/NetworkDays(MARCH-DECEMBER 31ST(218))=71,914.70

Is this syntax correct?

Yes, it works for the months that are the starting month, but the months in between become the problem. I'm sorry I don't really speak excel language and I struggle to put thoughts into comprehensive sentences (see title of thread as example). I want a formula to work out that from march till dec it's 746,543.08/218 and multiply that by Networking days in each month to get the monthly total. But then in June, it somehow should pick up that it should be (746,543.08/218 + 111,823.04/152) and multiply it by net working days for each month.

I tried to use IF: If current month is greater than previous month, return current month (SUMPRODUCT for anything equal to or greater than the date which should give for e.g 746,543.08 in March), if it's less return the difference. But then I couldn't figure out how to keep the previous months result and add it with the difference (Note the difference has to be divided by a different total of Networking days for the year....) anyway, I accidently deleted the data so I can't show my work and I can't recreate it......

Got it with good ol' IF,

Copy each of the formulas across, except for the last sum formula which is just the "Check" total;

Here is the link to my mock up if you needed a copy of the data;

Book1
ABCDEFGHIJKLMN
27Months01-01-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19Check
28NETWORK MONTH232021222320232221232122
29NETWORK YR26123821819717515213210987664322
30Monthly Costs 746,543.08111,823.057,506.3319,628.16
31 3,424.513,424.513,424.514,160.194,160.194,229.054,229.054,526.454,526.454,526.45Check
32Total Cost 71,914.7075,339.2178,763.7283,203.7595,684.3193,039.1688,810.11104,108.3395,055.4399,581.88885,500.62
Sheet1
Cell Formulas
RangeFormula
C27=EOMONTH(B27,0)+1
B28=NETWORKDAYS(B\$27,EOMONTH(B\$27,0))
B29=NETWORKDAYS(B\$27,EOMONTH(\$M\$27,0))
B30=IF(SUMIFS(\$C\$2:\$C\$25,\$A\$2:\$A\$25,">="&B\$27,\$A\$2:\$A\$25,"<="&EOMONTH(B\$27,0))=0,"",SUMIFS(\$C\$2:\$C\$25,\$A\$2:\$A\$25,">="&B\$27,\$A\$2:\$A\$25,"<="&EOMONTH(B\$27,0)))
B31=IF(ISNUMBER(B30),(B30/B29)+A31,A31)
B32=B31*B28
N32=SUM(B32:M32)

Last edited:
Yes thank you this works perfectly!! Thank you so much

Replies
3
Views
198
Replies
8
Views
199
Replies
2
Views
232
Replies
6
Views
366
Replies
2
Views
271

1,203,464
Messages
6,055,577
Members
444,799
Latest member
CraigCrowhurst

### 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.

### Which adblocker are you using?

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

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