Excel array question - spill VOWD based on forecast month

Question

Hi,Can I have your help on a spilled question?I have the following table with information.Fore MonthJan-26Feb-26Mar-26Apr-26May-26Jun-26Jul-26Aug-26Sep-26Oct-26Nov-26Dec-26VOWD2---3,98315,20311,7792,083-1,742,1621,696,156116,2671,162,366-2--100,000200,000--500,000650,000300,000-466,000--3---3,98315,20311,7792,08310,0001,742,1621,696,156116,2671,152,366-3--100,000200,000-250,000250,000650,000-400,000450,000-100,0004---2,00015,20311,7792,08310,0001,742,1621,696,156116,267152,3662,0004--100,000200,000-250,000250,000650,000-400,000350,000-300,000I would like to have a spilled array formula in VOWD column and the expected result is right there now. The logic is to grab the first 2 columns and sum up the amounts for the first two rows of data, and grab first three columns and sum up for the third and forth rows of data and etc.Thanks,Anna

nikolinode · Answer

=MAKEARRAY(ROWS(A2:A7), 1, LAMBDA(r,c,LET(fore_month, INDEX(A2:A7, r),SUMPRODUCT(N(COLUMN(B1:M1)&lt;=COLUMN(B1)+fore_month-1), INDEX(B2:M7, r, 0)))))&nbsp;Based on your description, the logic is to sum the values from the first N months (where N equals the "Fore Month" value) for each pair of rows.

patrick2788 · Answer

My approach is to use a mesh grid of column coordinates and pick out what you need. MGridλ is a generalized function that returns row or column numbers. Here it's returning column numbers.=LET(
    j, MGridλ(MonthlyData, 1),
    mdata, IF(j &lt;= ForeMonth, MonthlyData, 0),
    totals, BYROW(mdata, SUM),
    totals
)&nbsp;This is what it looks like step-by-step:&nbsp;

olufemi7 · Answer

Hello caltana​,Use this dynamic spill formula for VOWD: =MAP(A2:A7, B2:M7, LAMBDA(fm, row, SUM(IFERROR(--TAKE(row, fm), 0)))) – MAP processes each row, fm is the Fore Month, row is the monthly data, TAKE(row, fm) picks the first fm months, IFERROR(--…,0) converts any "-" to 0, and SUM adds them. It spills automatically, sums the first N months per Fore Month, and needs no helper columns.

snowman55 · Answer

There is no need to use a LAMBDA function to accomplish this.&nbsp; The OFFSET function will suffice. See this workbook: 2026-02-22 Anna summing variable ranges.xlsx

Forum Discussion

Excel array question - spill VOWD based on forecast month

4 Replies

Fore Month	Jan-26	Feb-26	Mar-26	Apr-26	May-26	Jun-26	Jul-26	Aug-26	Sep-26	Oct-26	Nov-26	Dec-26	VOWD
2	-	-	-	3,983	15,203	11,779	2,083	-	1,742,162	1,696,156	116,267	1,162,366	-
2	-	-	100,000	200,000	-	-	500,000	650,000	300,000	-	466,000	-	-
3	-	-	-	3,983	15,203	11,779	2,083	10,000	1,742,162	1,696,156	116,267	1,152,366	-
3	-	-	100,000	200,000	-	250,000	250,000	650,000	-	400,000	450,000	-	100,000
4	-	-	-	2,000	15,203	11,779	2,083	10,000	1,742,162	1,696,156	116,267	152,366	2,000
4	-	-	100,000	200,000	-	250,000	250,000	650,000	-	400,000	350,000	-	300,000