Distributing Amount over Months by Criteria - Formula Help

[RicardoCubed]

Hi out there in excel land. I am looking for help with a formula (non VBA) for E2 copied down and across to distribute amount by criteria selected. Example below with results.
Appreciate in advance any help.
Thank you.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
Monthly
Monthly
Quarterly-End
Monthly
Quarterly-Beginning
Monthly
Quarterly-End

<tbody>
[TD="align: center"]1
[/TD]
[TD="align: center"]Months in Contract
[/TD]
[TD="align: center"]Month Start
[/TD]
[TD="align: center"]Payment Frequency
[/TD]
[TD="align: center"]Amount
[/TD]
[TD="align: center"]Jan
[/TD]
[TD="align: center"]Feb
[/TD]
[TD="align: center"]Mar
[/TD]
[TD="align: center"]Apr
[/TD]
[TD="align: center"]May
[/TD]
[TD="align: center"]Jun
[/TD]
[TD="align: center"]Jul
[/TD]
[TD="align: center"]Aug
[/TD]
[TD="align: center"]Sep
[/TD]
[TD="align: center"]Oct
[/TD]
[TD="align: center"]Nov
[/TD]
[TD="align: center"]Dec
[/TD]
[TD="align: center"]Total
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"] 2,247.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 2,247
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 2,247
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]4
[/TD]

[TD="align: center"] 19,764.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 3,294
[/TD]
[TD="align: right"] 3,294
[/TD]
[TD="align: right"] 3,294
[/TD]
[TD="align: right"] 3,294
[/TD]
[TD="align: right"] 3,294
[/TD]
[TD="align: right"] 3,294
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 19,764
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"] 45,444.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 11,361
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 11,361
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 11,361
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 11,361
[/TD]
[TD="align: right"] 45,444
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]11
[/TD]

[TD="align: center"] 11,271.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 3,757
[/TD]
[TD="align: right"] 3,757
[/TD]
[TD="align: right"] 7,514
[/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"]11
[/TD]
[TD="align: right"]3
[/TD]

[TD="align: center"] 36,190.00
[/TD]
[TD="align: right"] 9,048
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 9,048
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 9,048
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 9,048
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 36,190
[/TD]

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

[TD="align: center"] 19,992.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 2,856
[/TD]
[TD="align: right"] 2,856
[/TD]
[TD="align: right"] 2,856
[/TD]
[TD="align: right"] 2,856
[/TD]
[TD="align: right"] 2,856
[/TD]
[TD="align: right"] 2,856
[/TD]
[TD="align: right"] 17,136
[/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"]11
[/TD]
[TD="align: right"]6
[/TD]

[TD="align: center"] 36,190.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 9,048
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 9,048
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 9,048
[/TD]
[TD="align: right"] 27,143
[/TD]

</tbody>

Sheet1​

 

[VBA Geek]

hi, there is a bit of inconsistency in the data provided

when you have either Quarter End or Quarter Beginning what does Month Start represent? for rows 6 and 8 it looks like it represents the month, for row 4 it represents the quarter number. Row 4 is what would make more sense...

 

[RicardoCubed]

Thanks for the response. I was attempting to illustrate that in rows 6 & 8 payment is due at quarter beginning (row 6) or end (row 8) while the contract starts in month 3 (Mar) and 6 (Jun) respectively. Row 4 is straightforward as you stated with contract starts on month 1, payment due end of the first quarter March.
Make sense?
Appreciate your time and help!

 

[VBA Geek]

I believe that for a formula to work correctly, when column C is Quarterly, then column B should represent the Quarter Number

so for quarter beginning: 1(Jan) 2(apr) 3 (jul) 4(oct)
quarter end 1(march), 2(june) 3(sept) 4(dec)

or you can say that column B always represents a month, so when column C is quarterly, the formula will start inputting values starting on that month and then each 3 months

 

[RicardoCubed]

Your last sentence is where my head is at and hoping for formula that could do just that.
Appreciate suggestions/help with formula to achieve same.

Thanks and looking forward to thoughts/approach/formula help.

 

[VBA Geek]

you can do it with this formula. also column A, when col C is Quarterly, it needs to be a multiple of 3 i assume

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">COLUMNS(<font color="Green">$E2:E2</font>)>=$B2,COLUMNS(<font color="Green">$E2:E2</font>)<$A2+$B2</font>),IF(<font color="Red">AND(<font color="Green">LEFT(<font color="Purple">$C2,9</font>)="quarterly",MOD(<font color="Purple">COLUMNS(<font color="Teal">$E:E</font>)-$B2,3</font>)</font>),"",$D2/$A2*IF(<font color="Green">LEFT(<font color="Purple">$C2,9</font>)="quarterly",3,1</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Months in Contract	Month Start	Payment Frequency	Amount	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
2	1	2	Monthly	2,247.00		2,247											2,247
3	6	4	Monthly	19,764.00				3,294	3,294	3,294	3,294	3,294	3,294				19,764
4	12	3	Quarterly-End	45,444.00			11,361			11,361			11,361			11,361	45,444
5	3	11	Monthly	11,271.00											3,757	3,757	7,514
6	12	1	Quarterly-Beginning	36,190.00	9,048			9,048			9,048			9,048			36,190
7	7	5	Monthly	19,992.00					2,856	2,856	2,856	2,856	2,856	2,856	2,856		17,136
8	12	6	Quarterly-End	36,190.00						9,048			9,048			9,048	27,143
Sheet1

 

[RicardoCubed]

Thanks so much!
I was trying to time the quarters to fiscal quarters not 3 month increments, not that my data set was layout in that fashion.
However I think I can live with this and modify my approach.
Very much appreciate your time and help with this!!!!
Regards and have a wonderful day!!