Dynamic sum repeat every 12 columns based on an array

[gtirrell]

I want this formula to add costs (from column Assumptions!H60:H649) in between a start date/month (Assumptions!J60:J649) and end date/month (Assumptions!L60:L649) that correspond to a certain cost category (the category can be picked in Assumptions!C60:C649, but they are added into a summary row with the category listed in Accruals!$B$85 for example). Monthly dates on the Accruals sheet are spread horizontally across D5:BK5 (60 months), so this formula will be dragged to the right (also down for some other categories).

The Mapping reference is to how often this will repeat. This mapping reference is for an "annual" repeat, I also have a "quarterly" repeat in this formula that just replaces the 12 with a 3. There's probably a more elegant way to do that part but this works.

My issue is that I know MONTH(Assumptions!$J$64) (bolded below) needs to reference J60:J649 so all the costs that should start in a particular month are included in this month's cell (Accruals!D85 in this example), but I'm not sure how to make that part of the formula work with that array.

=...+IFERROR(IF(MOD(MONTH(Accruals!D$5)-MONTH(Assumptions!$J$64),12)=0,INDEX(Assumptions!$H$60:$H$649,MATCH(1,IF(Accruals!D$5>=Assumptions!$J$60:$J$649,IF(Accruals!D$5<=Assumptions!$L$60:$L$649,1)),0)),0),0)*IFERROR((MATCH(Mapping!$H$6,Assumptions!$K$60:$K$649,0))/(MATCH(Mapping!$H$6,Assumptions!$K$60:$K$649,0)),0)

 

[KRice]

You want to add costs found in Assumptions!H60:H649 subject to some conditions.
One of those conditions involves matching a cost category to those listed in Assumptions!C60:C649. Where is the cost category that should be matched?
Two of the conditions involve the "start date/month" and "end date/month" found in Assumptions!J60:J649 and Assumptions!L60:L649, respectively. What is meant by "start date/month"? Is it a date, such as 3/15/2020, a month such as "March", or something else?
You mention that "monthly dates" are found in Accruals!D5:BK5. What is a monthly date?...similar question to the one I just asked, but the representation may matter.

In the formula you posted, you check whether the "monthly date" in Accruals!D$5 is >=Assumptions!$J$60:$J$649, and for those where this is TRUE, you then check whether the same "monthly date" in Accruals!D$5 <=Assumptions!$L$60:$L$649. What do want to achieve here? Do you want to ensure that Accruals!D$5 lies within the date range described on certain rows in Assumptions!$J$60:$J$649 and Assumptions!$L$60:$L$649, or do you want to find rows in Assumptions where the start dates occurs in the same month/year found in Accruals!D5:BK5, or are you interested in something else?

Regarding your question about an array with the MONTH function, you could reference a range in the function, but I doubt that is what you want. You would get a long array of numbers 1 through 12. It isn't clear what you want to do with this part of the formula: IF(MOD(MONTH(Accruals!D$5)-MONTH(Assumptions!$J$64),12)=0
Are you including another condition involving the start date? Are you interested in summing all January's together, and all February's together, etc? Or summing all months in the same year?

The second half of the formula posted doesn't appear to be doing anything other than returning a 1 if a match is found and a 0 if a match is not found: IFERROR((MATCH(Mapping!$H$6,Assumptions!$K$60:$K$649,0))/(MATCH(Mapping!$H$6,Assumptions!$K$60:$K$649,0)),0)

Since you are using Excel 365, I suspect a formula with one or possibly two FILTER functions will efficiently extract the relevant costs for summing, but more details are needed in order to advise.

 

[gtirrell]

First of all thanks for the well thought out clarifying questions.

• The cost category in Assumptions!C60:C649 (a pick list) is matched to 16 category choices in Accruals!B84:B99
• The start and end dates are also data validated pick lists, so the user picks Month-YY, the value of which is always the first of the month they pick. So there's no entering 24-June-2022, the cost just needs to be accounted for hitting the books in June-22. This list they pick from are the dates listed in Accruals!D5:BK5.
• I don't need to ensure that Accruals!D$5 lies within the date range described in Assumptions!$J$60:$J$649 and Assumptions!$L$60:$L$649; they are populated from the same data validated list.
• This formula will be repeated across Accruals!D84:BK99, across all the dates (columns) and categories (rows). I want that array to find the cost in Assumptions!H60:H649 and category in Assumptions!C60:C649, repeating between the start (Assumptions!J60:J649) and end dates (Assumptions!L60:L649), and have the cost repeat across the matched category row in Accruals.
  • I'll give an example of what I want to achieve for this formula of the cost repeating annually. Let's say I own 2 cars and need to account for the cost of registering them. Car1 gets registered every June, and Car2 gets registered every February. On the Assumptions sheet, I want to pick the "Registration" cost category, enter the cost for this car ($50), have the cost start in June-22 and have it repeat until June-25 (end date). On the next line in Assumptions when accounting for Car2, I pick "Registration" again, enter the cost ($75), and have the cost start in February-23 and end in February-25. If I buy a 3rd Car that also gets registered in June, I need to be able to enter the cost ($100) on another line in Assumptions to hit from June-23 to June-26. So on the Accruals sheet the Registration line will have $50 in June-22, and $150 in June-23. Like I mentioned there are other components to this formula like quarterly charges (let's say oil changes happen quarterly), and one time fees (repairs). I hope this helps conceptually, but if not please disregard.
• Regarding you question involving the MOD part of the formula, this is supposed to achieve the "repeating" part of the function, to have it repeat every 12 columns. It does do this, but only for the first line it encounters in Assumptions!60:649 (and it does not take the category from Assumptions!C60:C649 into account right now). So I want it to sum all the costs in January-23 together (and then repeated for all individual months across a few years). In case this helps, here is the full formula I have so far:
  • =SUMIFS(Assumptions!$H$60:$H$649,Assumptions!$C$60:$C$649,Accruals!$B86,Assumptions!$J$60:$J$649,Accruals!D$5,Assumptions!$K$60:$K$649,Mapping!$H$3)
    • For one-time costs (Mapping!H3 is "Once")
  • +IFERROR(INDEX(Assumptions!$H$60:$H$649,MATCH(1,IF(Accruals!D$5>=Assumptions!$J$60:$J$649,IF(Accruals!D$5<=Assumptions!$L$60:$L$649,1)),0)),0)*IFERROR((MATCH(Mapping!$H$4,Assumptions!$K$60:$K$649,0))/(MATCH(Mapping!$H$4,Assumptions!$K$60:$K$649,0)),0)
    • For monthly repeats (Mapping!H4 is "Monthly")
  • +IFERROR(IF(MOD(MONTH(Accruals!D$5)-MONTH(Assumptions!$J$64),3)=0,INDEX(Assumptions!$H$60:$H$649,MATCH(1,IF(Accruals!D$5>=Assumptions!$J$60:$J$649,IF(Accruals!D$5<=Assumptions!$L$60:$L$649,1)),0)),0),0)*IFERROR((MATCH(Mapping!$H$5,Assumptions!$K$60:$K$649,0))/(MATCH(Mapping!$H$5,Assumptions!$K$60:$K$649,0)),0)
    • Quarterly (Mapping!H5), does repeat quarterly but only for the first quarterly cost it encounters in Assumptions
  • +IFERROR(IF(MOD(MONTH(Accruals!D$5)-INDEX(MONTH(Assumptions!$J$60:$J$649),MATCH(Accruals!$B86,Assumptions!$C$60:$C$649,0)),12)=0,INDEX(Assumptions!$H$60:$H$649,MATCH(1,IF(Accruals!D$5>=Assumptions!$J$60:$J$649,IF(Accruals!D$5<=Assumptions!$L$60:$L$649,1)),0)),0),0)*IFERROR((MATCH(Mapping!$H$6,Assumptions!$K$60:$K$649,0))/(MATCH(Mapping!$H$6,Assumptions!$K$60:$K$649,0)),0)
    • Annual, same issue with Quarterly
• Because this is a 4 part formula, I want it to return a 0 if no cost of a certain type of repeat is supposed to hit in a given month. I.e. A one time cost and a quarterly cost could hit in the same month for the same category, so they are summed for that month, but then 3 months later only the quarterly cost is carried forward.
• Lastly, I'm trying to build this to be "automatic", so the user does not have to filter anything themselves. I'm not actually familiar with the FILTER function, but I'll look into it in the meantime.

 

[KRice]

Thank you for the explanation. It helped clarify a number of issues, and my initial thought about what you wanted was not correct. I may not yet completely follow it, but have a look and let me know if this is close. I'm not sure what the Mapping sheet is for...I didn't use one here. I generated some data for the Assumptions sheet, shown below. If it isn't already part of the Assumptions input table, I would add a column for the frequency of the cost and I would strongly encourage you to apply some data validation to the date cells, probably the End Dates. Then based on the frequency and the Start Date, the End Date would be limited to certain values. For example, if "Annual", then the End Date would be EDATE(start date, n*11), where n is a positive integer...and this would prevent the End Date from spilling into the year after the end of the payment/cost term. For one-time payments, the Start and End Dates would be forced to be the same. I mention this because I haven't implemented any checks for these types of issues in the formula shown further below, but it would be simpler to address it at the input stage.

MrExcel_20220624.xlsx
	C	D	E	F	G	H	I	J	K	L
59	CostCategory					Costs	Frequency	Start		End
60	c cat4					1	Annual	2/1/2020		2/1/2022
61	c cat3					2	Monthly	2/1/2020		3/1/2022
62	c cat6					3	Monthly	3/1/2020		4/3/2020
63	c cat3					7	Once	4/1/2020		4/1/2020
64	c cat3					6	Quarterly	5/1/2020		1/1/2023
65	c cat3					5.5	Annual	1/1/2020		1/1/2021
66	c cat2					77	Annual	6/1/2020		5/1/2024
67	c cat1					11.32	Quarterly	3/1/2020		7/1/2024
68	c cat3					5.5	Annual	1/1/2020		1/1/2021
69	c cat3					5.5	Annual	1/1/2020		1/1/2021
70	c cat7					5.5	Annual	1/1/2020		1/1/2021
71	c cat7					57	Quarterly	8/1/2020		1/1/2023
72	c cat8					5.5	Annual	1/1/2020		1/1/2021
73	c cat13					100	Once	7/1/2020		7/1/2020
74	c cat13					22	Monthly	5/1/2020		1/1/2023
75	c cat13					16	Quarterly	1/1/2020		10/1/2023
76	c cat13					1000	Annual	7/1/2020		6/1/2024
77	c cat16					10	Monthly	8/1/2020		6/1/2022
Assumptions

Then the formula on the Accruals worksheet uses a FILTER function to extract columns H:L for all matching cost category entries, and that array (called fcat, short for filtered category array) is used to set the onset, frequency, and termination of each cost item. Where multiple items coincide on the same month, they are summed. This single formula is entered in D84 and dragged down and across the table. I tried to do this with a spilling array formula but ran into some difficulties attempting to create multiple frequency arrays for single cells. There may be a more elegant way to do this, but I opted for an approach that relies on column counting instead. Let me know how this works and if you discover any issues with it.

MrExcel_20220624.xlsx
	A	B	C	D	E	F	G
5				Jan-20	Feb-20	Mar-20	Apr-20
6
83
84		c cat1		0	0	11.32	0
85		c cat2		0	0	0	0
86		c cat3		16.5	2	2	9
87		c cat4		0	1	0	0
88		c cat5		0	0	0	0
89		c cat6		0	0	3	3
90		c cat7		5.5	0	0	0
91		c cat8		5.5	0	0	0
92		c cat9		0	0	0	0
93		c cat10		0	0	0	0
94		c cat11		0	0	0	0
95		c cat12		0	0	0	0
96		c cat13		16	0	0	16
97		c cat14		0	0	0	0
98		c cat15		0	0	0	0
99		c cat16		0	0	0	0
Accruals

Cell Formulas
Range	Formula
D5:BK5	D5	=DATE(2020,SEQUENCE(1,60),1)
D84:G99	D84	=LET(fcat,FILTER(Assumptions!$H$60:$L$649,Assumptions!$C$60:$C$649=$B84), fcat_cost,INDEX(fcat,,1), fcat_freq,INDEX(fcat,,2), fcat_sd,INDEX(fcat,,3), fcat_ed,INDEX(fcat,,5), strtnum,MATCH(fcat_sd,$D$5:$BK$5,0), freq, XLOOKUP(fcat_freq,{"annual";"quarterly";"monthly";"once"},{12;3;1;1}), IFERROR(SUM(fcat_cost*IF((D$5>=fcat_sd)*(D$5<=fcat_ed)*IF((fcat_freq="annual")+(fcat_freq="quarterly")=1,MOD(COLUMNS($D:D)-strtnum+1,freq)=1,1),1,0)),0))
Dynamic array formulas.

 

[KRice]

I believe the formula in my last post has an error, but I was able to get a more preferred approach working...shown below. This approach creates an array for each cost category...an array that contains each relevant costs distributed over the calendar according to the start and end dates and payment frequency. Those costs are then summed by column. The main formula is in D84, which is pulled down only so that it can operate on each of the categories. The results automatically spill to the right. To provide more insight into how the formula works, I've left row 101 and below in this example so that you can see how, for category "c cat3" in B103, the formula pulls relevant data from the Assumptions sheet, and then uses that subset of data to create small arrays to determine when the cost/payment amounts should be applied. After confirming that this checks out, you can delete everything below row 100 and just use the D84 formula. Please let me know if you discover any issues or have any questions.

MrExcel_20220624.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
5				Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21
6
83
84		c cat1		0	0	11.32	0	0	11.32	0	0	11.32	0	0	11.32	0	0	11.32	0	0	11.32	0	0
85		c cat2		0	0	0	0	0	77	0	0	0	0	0	0	0	0	0	0	0	77	0	0
86		c cat3		16.5	2	2	9	8	2	2	8	2	2	8	2	7.5	8	2	2	8	2	2	8
87		c cat4		0	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
88		c cat5		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
89		c cat6		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
90		c cat7		5.5	0	0	0	0	0	0	57	0	0	57	0	0	57	0	0	57	0	0	57
91		c cat8		5.5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
92		c cat9		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
93		c cat10		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
94		c cat11		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
95		c cat12		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
96		c cat13		16	0	0	16	22	22	1138	22	22	38	22	22	38	22	22	38	22	22	1038	22
97		c cat14		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
98		c cat15		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
99		c cat16		0	0	0	0	0	0	0	10	10	10	10	10	10	10	10	10	10	10	10	10
100
101													1	2	3	4	5	6	7	8	9	10	11
102		Category		Amt	Freq	Start Date		End Date	start	End Date idx	end		1/1/20	2/1/20	3/1/20	4/1/20	5/1/20	6/1/20	7/1/20	8/1/20	9/1/20	10/1/20	11/1/20
103		c cat3		2	Monthly	2/1/2020	0	3/1/2022	2	27	27		0	2	2	2	2	2	2	2	2	2	2
104				7	Once	4/1/2020	0	4/1/2020	4	4	4		0	0	0	7	0	0	0	0	0	0	0
105				6	Quarterly	5/1/2020	0	1/1/2023	5	37	36		0	0	0	0	6	0	0	6	0	0	6
106				5.5	Annual	1/1/2020	0	1/1/2021	1	13	12		5.5	0	0	0	0	0	0	0	0	0	0
107				5.5	Annual	1/1/2020	0	1/1/2021	1	13	12		5.5	0	0	0	0	0	0	0	0	0	0
108				5.5	Annual	1/1/2020	0	1/1/2024	1	49	48		5.5	0	0	0	0	0	0	0	0	0	0
Accruals

Cell Formulas
Range	Formula
D5:BK5,M102:BT102	D5	=DATE(2020,SEQUENCE(1,60),1)
D84:BK99	D84	=LET(cat,Assumptions!$C$60:$C$649, data,Assumptions!$H$60:$L$649, daterng,$D$5:$BK$5, fcat,FILTER(data,cat=$B84), amt,INDEX(fcat,,1), start,MATCH(INDEX(fcat,,3),daterng,0), endidx,MATCH(INDEX(fcat,,5),daterng,0), freq,XLOOKUP(INDEX(fcat,,2),{"annual";"quarterly";"monthly";"once"},{12;3;1;1}), end,FLOOR(endidx,freq), ma,MAKEARRAY(COUNT(amt),60,LAMBDA(r,c,IF(c<INDEX(start,r),0,IF(c>INDEX(end,r),0,IF(MOD(c-INDEX(start,r),INDEX(freq,r))=0,INDEX(amt,r),0) )))), IFERROR(BYCOL(ma,LAMBDA(column,SUM(column))),SEQUENCE(,60,0,0) ) )
D103:H108	D103	=FILTER(Assumptions!$H$60:$L$649,Assumptions!$C$60:$C$649=$B103)
I103:I108	I103	=MATCH(INDEX($D103#,,3),$D$5:$BK$5,0)
J103:J108	J103	=MATCH(INDEX($D103#,,5),$D$5:$BK$5,0)
K103:K108	K103	=FLOOR(J103#,XLOOKUP(INDEX(D103#,,2),{"annual";"quarterly";"monthly";"once"},{12;3;1;1}))
M101:BT101	M101	=SEQUENCE(,60)
M103:BT108	M103	=MAKEARRAY(COUNT(INDEX(D103#,,1)),60,LET(start,I103#, freq,XLOOKUP(INDEX(D103#,,2),{"annual";"quarterly";"monthly";"once"},{12;3;1;1}), end,FLOOR(J103#,freq), LAMBDA(r,c,IF(c<INDEX(start,r),0,IF(c>INDEX(end,r),0,IF(MOD(c-INDEX(start,r),INDEX(freq,r))=0,INDEX(INDEX(D103#,,1),r),0) )))))
Dynamic array formulas.

 

[gtirrell]

First of all thank you so much for all the effort put into solving this problem.

I have put data validation on the start and end dates so that only D5:BK5 may be chosen, as well as the repeat frequency.

It looks like what you have in the sample table above is an accurate example and recreation of what I have described, however when I copied your formula in I get a SPILL error. I only copied it in, I did not add in anything you had below row 99 in your example - does the D84 formula rely on any of the formulas further down? Your skill with Excel is miles and miles ahead of my own, I thought the D84 formula would have to reference the D5 date specifically? I assume I'm not following a basic assumption of how you setup the table above.

Thanks again.

 

[KRice]

Usually the SPILL error occurs when other cells that are needed to accommodate the spill range contain something. Check that all 59 cells to the right of D84 are clear. The formula should create the entire 60-element array that covers your 5-year monthly calendar. Let me know if that clears up the spill issue.

About the reference to D5, I took a slightly different approach. The daterng defined in the LET function is an array of all 60 month-year headings. And "start" and "endidx" in the LET function are arrays indicating the column index positions in the daterng array that correspond to the start and end dates in your source data table. So rather than using a formula to populate the table by referring to each date in the column heading, it is more convenient to use the column index arrays. As a side note--and this may be important if it does not do what you want--the "endidx" array is used to create a new array called "end" that considers the frequency of the costs/payments and adjusts the last date. In my example, I entered a cost to be paid annually, but indicated the start and end dates covered 13 months. This "feature" (if you can call it that) assumes that the user really meant to cover 12 months (e.g., a start of 1/1/2020 and an end of 12/1/2020 would be 12 months for a payment made in advance on 1/1/2020). And if the user entered 1/1/2020 to 1/1/2021, the 1/1/2021 is ignored and the end date is effectively taken as 12/1/2020. Let me know if that is intended or if you have some other type of data validation where this scenario could not occur.

 

[gtirrell]

Thank you, the formula now does work when I deleted everything in E84:BK99. I tested it out with some sample amounts and everything seems to work exactly as intended except for the "Annual" repeat (Once, Monthly, and Quarterly are perfect). Annual charges do appear on the start date, but only the start date. I tried entering the end date at 12, 13, and 14 months after the start date and this did not solve the problem.

I'm not sure if this has anything to do with it, but my "freq" column is inbetween the start and end date, so I did adjust the column reference in the "freq" line from 2 to 4. I also checked that "Annual" is exactly the same in both locations without any leading or trailing spaces. I don't understand why Annual wouldn't work when Quarterly does. In any case, I think the "feature" you included is great, if the user enters an end date 13, 14, 15 etc months after the start date I would still want it to only repeat 12 months after the start date (e.g. Jan-20 and Jan-21).

I don't think this is the issue, again, since Quarterly freq works, but I did make the months populate in D5:BK5 with just a very simple =EDATE(D5,1) dragged to the right, with D5 populating from a start date entered by the user on a different sheet. Hopefully that has nothing to do with the Annual issue.

 

[KRice]

The LET function includes "data", which brings in columns H60:L649 from the Assumptions sheet. Are you saying that H:L contain Costs, blank, Start, Frequency, End, in that order...so that only the column index for frequency would change from 2 to 4 and none of the other column indexes are affected (I assumed Costs, Frequency, Start, blank, End)? If this is the case, then I agree...changing the index in one place would be required (this is the advantage of the LET function in that the defined name "freq" changes due to this single edit and that change carries forward anywhere else "freq" is referenced). And this change shouldn't create any issues.

And you've confirmed the word "Annual" is being used consistently in both the formula and in the Assumptions table...and the EDATE method for populating the column headings should not be an issue provided the starting date is the 1st of a month (is that the case?). So let's examine how the Annual entries are made. Can you provide some examples? In my working example, every start and end date is assumed to be on the 1st of a month. Is that actually the case? In my example Assumptions table shown in post #4, I included several lines with "Annual" entries and all of them populate the correct cells according to the rule I mentioned (where the last entry must cover an entire year, and if the end date entered by the user spills into the following year but is not sufficient to cover another 12 months, then the last full year's end date is used).

At the bottom of the approach shown in post #5, I included some explanatory cells to show how various line items in the Assumptions table are aggregated by the formula. The bottom line is a multi-year "annual" entry. Do you see any differences between it and how your actual data appears?

MrExcel_20220624.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
101												1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
102	Category		Amt	Freq	Start Date		End Date	start	End Date idx	end		1/1/20	2/1/20	3/1/20	4/1/20	5/1/20	6/1/20	7/1/20	8/1/20	9/1/20	10/1/20	11/1/20	12/1/20	1/1/21	2/1/21	3/1/21	4/1/21	5/1/21	6/1/21	7/1/21	8/1/21	9/1/21	10/1/21	11/1/21	12/1/21	1/1/22	2/1/22	3/1/22	4/1/22	5/1/22	6/1/22
103	c cat3		2	Monthly	2/1/2020	0	3/1/2022	2	27	27		0	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	0	0	0
104			7	Once	4/1/2020	0	4/1/2020	4	4	4		0	0	0	7	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
105			6	Quarterly	5/1/2020	0	1/1/2023	5	37	36		0	0	0	0	6	0	0	6	0	0	6	0	0	6	0	0	6	0	0	6	0	0	6	0	0	6	0	0	6	0
106			5.5	Annual	1/1/2020	0	1/1/2021	1	13	12		5.5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
107			5.5	Annual	1/1/2020	0	1/1/2021	1	13	12		5.5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
108			5.5	Annual	1/1/2020	0	1/1/2024	1	49	48		5.5	0	0	0	0	0	0	0	0	0	0	0	5.5	0	0	0	0	0	0	0	0	0	0	0	5.5	0	0	0	0	0
Accruals

Cell Formulas
Range	Formula
D103:H108	D103	=FILTER(Assumptions!$H$60:$L$649,Assumptions!$C$60:$C$649=$B103)
I103:I108	I103	=MATCH(INDEX($D103#,,3),$D$5:$BK$5,0)
J103:J108	J103	=MATCH(INDEX($D103#,,5),$D$5:$BK$5,0)
K103:K108	K103	=FLOOR(J103#,XLOOKUP(INDEX(D103#,,2),{"annual";"quarterly";"monthly";"once"},{12;3;1;1}))
M101:BT101	M101	=SEQUENCE(,60)
M102:BT102	M102	=DATE(2020,SEQUENCE(1,60),1)
M103:BT108	M103	=MAKEARRAY(COUNT(INDEX(D103#,,1)),60,LET(start,I103#, freq,XLOOKUP(INDEX(D103#,,2),{"annual";"quarterly";"monthly";"once"},{12;3;1;1}), end,FLOOR(J103#,freq), LAMBDA(r,c,IF(c<INDEX(start,r),0,IF(c>INDEX(end,r),0,IF(MOD(c-INDEX(start,r),INDEX(freq,r))=0,INDEX(INDEX(D103#,,1),r),0) )))))
Dynamic array formulas.

 

[gtirrell]

Confirming that H:L contain Costs, blank, Start, Frequency, End in that order. And yes, the starting date is the first of the month. The data validation for Start and End on the Assumptions sheet (J60:J649 and L60:L649) is the list Accruals!D5:BK5 and are all the first of the month.

I made some more example entries and understand a little more of the nature of the issue, or just the fact it's not doing exactly what I intended. If I enter a start date of Feb-20 and end date Feb-21, the cost will only appear in Feb-20. Similarly, with start and end dates of Feb-20 to Feb-22, the cost will appear in Feb-20 and Feb-21, whereas I would want it to be more inclusive. In the last example, I would want the cost to appear 3 times, in Feb-20, Feb-21, and Feb-22. Do you know how to adjust this?

I think this is what you meant originally when you explained the "feature". You actually explained it perfectly and I just misunderstood. If the user enters 13 months I would assume the user is correct and a second annual payment would be due on the 13th month.

By the way, the cell formulas you're including are VERY helpful. I'm not familiar with them all, but this does a lot to help me understand and perhaps use them in the future in other settings.