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

[Kiriko]

Hopefully this is as easy as the title makes it seem:

I have the below data:

[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 101, bgcolor: transparent"]Month
[/TD]
[TD="width: 104, bgcolor: transparent"]Month Number
[/TD]
[TD="width: 136, bgcolor: transparent"]Total Cost
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="bgcolor: transparent, align: right"]3.00
[/TD]
[TD="bgcolor: transparent, align: right"]71,250.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="bgcolor: transparent, align: right"]3.00
[/TD]
[TD="bgcolor: transparent, align: right"]71,250.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="bgcolor: transparent, align: right"]3.00
[/TD]
[TD="bgcolor: transparent, align: right"]41,250.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="bgcolor: transparent, align: right"]3.00
[/TD]
[TD="bgcolor: transparent, align: right"]41,250.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Jun-19
[/TD]
[TD="bgcolor: transparent, align: right"]6.00
[/TD]
[TD="bgcolor: transparent, align: right"]16,988.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="bgcolor: transparent, align: right"]3.00
[/TD]
[TD="bgcolor: transparent, align: right"]13,904.21
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Oct-19
[/TD]
[TD="bgcolor: transparent, align: right"]10.00
[/TD]
[TD="bgcolor: transparent, align: right"]3,089.82
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Aug-19
[/TD]
[TD="bgcolor: transparent, align: right"]8.00
[/TD]
[TD="bgcolor: transparent, align: right"]7,506.33
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="bgcolor: transparent, align: right"]3.00
[/TD]
[TD="bgcolor: transparent, align: right"]78,750.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="bgcolor: transparent, align: right"]3.00
[/TD]
[TD="bgcolor: transparent, align: right"]78,750.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="bgcolor: transparent, align: right"]3.00
[/TD]
[TD="bgcolor: transparent, align: right"]30,000.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="bgcolor: transparent, align: right"]3.00
[/TD]
[TD="bgcolor: transparent, align: right"]30,000.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Oct-19
[/TD]
[TD="bgcolor: transparent, align: right"]10.00
[/TD]
[TD="bgcolor: transparent, align: right"]8,269.17
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Oct-19
[/TD]
[TD="bgcolor: transparent, align: right"]10.00
[/TD]
[TD="bgcolor: transparent, align: right"]8,269.17
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Jun-19
[/TD]
[TD="bgcolor: transparent, align: right"]6.00
[/TD]
[TD="bgcolor: transparent, align: right"]16,433.69
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Jun-19
[/TD]
[TD="bgcolor: transparent, align: right"]6.00
[/TD]
[TD="bgcolor: transparent, align: right"]35,444.25
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Jun-19
[/TD]
[TD="bgcolor: transparent, align: right"]6.00
[/TD]
[TD="bgcolor: transparent, align: right"]16,210.33
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Jun-19
[/TD]
[TD="bgcolor: transparent, align: right"]6.00
[/TD]
[TD="bgcolor: transparent, align: right"]26,746.78
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="bgcolor: transparent, align: right"]3.00
[/TD]
[TD="bgcolor: transparent, align: right"]76,301.25
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="bgcolor: transparent, align: right"]3.00
[/TD]
[TD="bgcolor: transparent, align: right"]71,250.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="bgcolor: transparent, align: right"]3.00
[/TD]
[TD="bgcolor: transparent, align: right"]53,166.38
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="bgcolor: transparent, align: right"]3.00
[/TD]
[TD="bgcolor: transparent, align: right"]40,120.16
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="bgcolor: transparent, align: right"]3.00
[/TD]
[TD="bgcolor: transparent, align: right"]24,650.54
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="bgcolor: transparent, align: right"]3.00
[/TD]
[TD="bgcolor: transparent, align: right"]24,650.54
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Total
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]885,500.59


[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 304"]
<tbody>[TR]
[TD="width: 101, bgcolor: transparent"]Starting Month
[/TD]
[TD="width: 104, bgcolor: transparent"]Month Number
[/TD]
[TD="width: 136, bgcolor: transparent"]Total Cost to start in:
[/TD]
[TD="width: 64, bgcolor: transparent"]NETWORKING DAYS
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]March
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent"]746,543.06
[/TD]
[TD="bgcolor: transparent, align: right"]218
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]June
[/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[TD="bgcolor: transparent"]111,823.04
[/TD]
[TD="bgcolor: transparent, align: right"]152
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]August
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent"]7,506.33
[/TD]
[TD="bgcolor: transparent, align: right"]109
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]October
[/TD]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[TD="bgcolor: transparent"]19,628.16
[/TD]
[TD="bgcolor: transparent, align: right"]66
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Total
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]885,500.59
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]



I need to do the below calculations:

[TABLE="width: 739"]
<tbody>[TR]
[TD="width: 339, bgcolor: transparent"]Calculation:
[/TD]
[TD="width: 116, bgcolor: transparent"]Month
[/TD]
[TD="width: 82, bgcolor: transparent"]NetWorking Days_19
[/TD]
[TD="width: 81, bgcolor: transparent"]Monthly Cost
[/TD]
[TD="width: 81, bgcolor: transparent"]Monthly Cost
[/TD]
[TD="width: 81, bgcolor: transparent"]Monthly Cost
[/TD]
[TD="width: 81, bgcolor: transparent"]Monthly Cost
[/TD]
[TD="width: 122, bgcolor: transparent"]Total Montly Cost 2019
[/TD]
[/TR]
[TR]
[TD="width: 339, bgcolor: transparent"][/TD]
[TD="width: 116, bgcolor: transparent"]Start in Month
[/TD]
[TD="width: 82, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="width: 81, bgcolor: transparent, align: right"]Jun-19
[/TD]
[TD="width: 81, bgcolor: transparent, align: right"]Aug-19
[/TD]
[TD="width: 81, bgcolor: transparent, align: right"]Oct-19
[/TD]
[TD="width: 122, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 339, bgcolor: transparent"][/TD]
[TD="width: 116, bgcolor: transparent"]Total Cost for the Year
[/TD]
[TD="width: 82, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]746,543.06
[/TD]
[TD="bgcolor: transparent"]111,823.04
[/TD]
[TD="bgcolor: transparent"]7,506.33
[/TD]
[TD="bgcolor: transparent"]19,628.16
[/TD]
[TD="width: 122, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NET WORKING DAYS
[/TD]
[TD="bgcolor: transparent, align: right"]261
[/TD]
[TD="bgcolor: transparent"]218
[/TD]
[TD="bgcolor: transparent"]152.00
[/TD]
[TD="bgcolor: transparent"]109.00
[/TD]
[TD="bgcolor: transparent"]66.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent, align: right"]Jan-19
[/TD]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent, align: right"]Feb-19
[/TD]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"](746/218)*NWD in Month
[/TD]
[TD="bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[TD="bgcolor: transparent"]71,914.70
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]71,914.70
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"](746/218)*NWD in Month
[/TD]
[TD="bgcolor: transparent, align: right"]Apr-19
[/TD]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="bgcolor: transparent"]75,339.21
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]75,339.21
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"](746/218)*NWD in Month
[/TD]
[TD="bgcolor: transparent, align: right"]May-19
[/TD]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[TD="bgcolor: transparent"]78,763.72
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]78,763.72
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"](746/218)+(111/152)*NWD in Month
[/TD]
[TD="bgcolor: transparent, align: right"]Jun-19
[/TD]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="bgcolor: transparent"]68,490.19
[/TD]
[TD="bgcolor: transparent"]14,713.56
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]83,203.75
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"](746/218)+(111/152)*NWD in Month
[/TD]
[TD="bgcolor: transparent, align: right"]Jul-19
[/TD]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[TD="bgcolor: transparent"]78,763.72
[/TD]
[TD="bgcolor: transparent"]16,920.59
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]95,684.31
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"](746/218)+(111/152)+(7/109)*NWD in Month
[/TD]
[TD="bgcolor: transparent, align: right"]Aug-19
[/TD]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="bgcolor: transparent"]75,339.21
[/TD]
[TD="bgcolor: transparent"]16,184.91
[/TD]
[TD="bgcolor: transparent"]1,515.04
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]93,039.16
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"](746/218)+(111/152)+(7/109)*NWD in Month
[/TD]
[TD="bgcolor: transparent, align: right"]Sep-19
[/TD]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[TD="bgcolor: transparent"]71,914.70
[/TD]
[TD="bgcolor: transparent"]15,449.24
[/TD]
[TD="bgcolor: transparent"]1,446.17
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]88,810.11
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"](746/218)+(111/152)+(7/109)+(19/66)*NWD in Month
[/TD]
[TD="bgcolor: transparent, align: right"]Oct-19
[/TD]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[TD="bgcolor: transparent"]78,763.72
[/TD]
[TD="bgcolor: transparent"]16,920.59
[/TD]
[TD="bgcolor: transparent"]1,583.91
[/TD]
[TD="bgcolor: transparent"]6,840.12
[/TD]
[TD="bgcolor: transparent"]104,108.33
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"](746/218)+(111/152)+(7/109)+(19/66)*NWD in Month
[/TD]
[TD="bgcolor: transparent, align: right"]Nov-19
[/TD]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[TD="bgcolor: transparent"]71,914.70
[/TD]
[TD="bgcolor: transparent"]15,449.24
[/TD]
[TD="bgcolor: transparent"]1,446.17
[/TD]
[TD="bgcolor: transparent"]6,245.32
[/TD]
[TD="bgcolor: transparent"]95,055.43
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"](746/218)+(111/152)+(7/109)+(19/66)*NWD in Month
[/TD]
[TD="bgcolor: transparent, align: right"]Dec-19
[/TD]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="bgcolor: transparent"]75,339.21
[/TD]
[TD="bgcolor: transparent"]16,184.91
[/TD]
[TD="bgcolor: transparent"]1,515.04
[/TD]
[TD="bgcolor: transparent"]6,542.72
[/TD]
[TD="bgcolor: transparent"]99,581.88
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]746,543.06
[/TD]
[TD="bgcolor: transparent"]111,823.04
[/TD]
[TD="bgcolor: transparent"]7,506.33
[/TD]
[TD="bgcolor: transparent"]19,628.16
[/TD]
[TD="bgcolor: transparent"]885,500.59
[/TD]
[/TR]
</tbody>[/TABLE]
To get the result in the below format:


[TABLE="width: 814"]
<tbody>[TR]
[TD="width: 130, bgcolor: transparent"]Month 2019
[/TD]
[TD="width: 46, bgcolor: transparent, align: right"]Jan-19
[/TD]
[TD="width: 49, bgcolor: transparent, align: right"]Feb-19
[/TD]
[TD="width: 70, bgcolor: transparent, align: right"]Mar-19
[/TD]
[TD="width: 70, bgcolor: transparent, align: right"]Apr-19
[/TD]
[TD="width: 70, bgcolor: transparent, align: right"]May-19
[/TD]
[TD="width: 70, bgcolor: transparent, align: right"]Jun-19
[/TD]
[TD="width: 70, bgcolor: transparent, align: right"]Jul-19
[/TD]
[TD="width: 70, bgcolor: transparent, align: right"]Aug-19
[/TD]
[TD="width: 70, bgcolor: transparent, align: right"]Sep-19
[/TD]
[TD="width: 77, bgcolor: transparent, align: right"]Oct-19
[/TD]
[TD="width: 70, bgcolor: transparent, align: right"]Nov-19
[/TD]
[TD="width: 70, bgcolor: transparent, align: right"]Dec-19
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 130, bgcolor: transparent"]NETWORKING DAYS
[/TD]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Check
[/TD]
[/TR]
[TR]
[TD="width: 130, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 130, bgcolor: transparent"]Total Cost
[/TD]
[TD="bgcolor: transparent"]-
[/TD]
[TD="bgcolor: transparent"]-
[/TD]
[TD="bgcolor: transparent"]71,914.70
[/TD]
[TD="bgcolor: transparent"]75,339.21
[/TD]
[TD="bgcolor: transparent"]78,763.72
[/TD]
[TD="bgcolor: transparent"]83,203.75
[/TD]
[TD="bgcolor: transparent"]95,684.31
[/TD]
[TD="bgcolor: transparent"]93,039.16
[/TD]
[TD="bgcolor: transparent"]88,810.11
[/TD]
[TD="bgcolor: transparent"]104,108.33
[/TD]
[TD="bgcolor: transparent"]95,055.43
[/TD]
[TD="bgcolor: transparent"]99,581.88
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]885,500.59





[/TD]
[/TR]
[TR]
[TD="width: 130, bgcolor: transparent"]Thank you to anyone who can and tries to help
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[RasGhul]

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
	A	B	C
1	Month	Month Number	Total Cost
2	Mar-19	3	71,250.00
3	Mar-19	3	71,250.00
4	Mar-19	3	41,250.00
5	Mar-19	3	41,250.00
6	Jun-19	6	16,988.00
7	Mar-19	3	13,904.21
8	Oct-19	10	3,089.82
9	Aug-19	8	7,506.33
10	Mar-19	3	78,750.00
11	Mar-19	3	78,750.00
12	Mar-19	3	30,000.00
13	Mar-19	3	30,000.00
14	Oct-19	10	8,269.17
15	Oct-19	10	8,269.17
16	Jun-19	6	16,433.69
17	Jun-19	6	35,444.25
18	Jun-19	6	16,210.33
19	Jun-19	6	26,746.78
20	Mar-19	3	76,301.25
21	Mar-19	3	71,250.00
22	Mar-19	3	53,166.38
23	Mar-19	3	40,120.16
24	Mar-19	3	24,650.54
25	Mar-19	3	24,650.54
Sheet1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
27	NETWORK MONTH	23	20	21	22	23	20	23	22	21	23	21	22
28	NETWORK YR	261	238	218	197	175	152	132	109	87	66	43	22
29	Monthly Costs			71,914.70			14,713.56		1,515.04		6,840.12
30	Months	01-01-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Check
31	Total Costs			71,914.70	71,914.70	71,914.70	86,628.26	86,628.26	88,143.30	88,143.30	94,983.42	94,983.42	94,983.42	850,237.46
Sheet1

Cell Formulas
Range	Formula
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)

 

[Kiriko]

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

 

[RasGhul]

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?

 

[Kiriko]

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

 

[RasGhul]

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;

https://docs.google.com/spreadsheets/d/1Ym53CVT9lmRxlrPkrA7wbF-W8YFXYQeIA1a608qoHg4/edit?usp=sharing

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
27	Months	01-01-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Check
28	NETWORK MONTH	23	20	21	22	23	20	23	22	21	23	21	22
29	NETWORK YR	261	238	218	197	175	152	132	109	87	66	43	22
30	Monthly Costs			746,543.08			111,823.05		7,506.33		19,628.16
31				3,424.51	3,424.51	3,424.51	4,160.19	4,160.19	4,229.05	4,229.05	4,526.45	4,526.45	4,526.45	Check
32	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.62
Sheet1

Cell Formulas
Range	Formula
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)

 

[Kiriko]

Yes thank you this works perfectly!! Thank you so much