Cannot make formula SPILL down

[DaveyP]

Hello,

I have been trying without success to update my spreadsheet with SPILLed formulas to reduce the file size.
The attached mini-sheet is a re-creation of a small portion of my spreadsheet (as it is several hundred rows and many worksheets).
My current formula in column K works fine. I can also make it work as a CSE formula (Col. L). However when I make it SPILL (Col. M) I get a #VALUE! error in all of the SPILLed cells.
I have tried various approaches i.e. using SUMPRODUCT instead of SUM and FILTER instead of XLOOKUP.
Any help would be appreciated. NOTE: I am using Excel 2021 (It has a number of the newer ARRAY functions, but not all of the functions that come with 365
Thx.

Gain Loss Calc v4.xlsx
	A	B		D							K	L	M	N		P		R				V		X		Z				AD		AF		AH				AL
1	Date	Stock		Date Purch							NON-ARRAY Gain/Loss	CSE Gain/Loss	SPILL ARRAY Gain/Loss			AQN_ExDivDate		AQN_PayDate				AQN_Div		BCE_ExDivDate		BCE_PayDate				BCE_Div		IHI_ExDivDate		IHI_PayDate				IHI_Div
2	2023-07-05	AQN		2022-06-01							0.87	0.87	#VALUE!			2023-03-30		2023-04-14				0.144		2023-06-14		2023-07-15				1.287		07-Jun-23		13-Jun-23				0.071
3	2023-07-05	BCE		2021-09-15							7.34	7.34	#VALUE!			2022-12-29		2023-01-13				0.240		2023-03-14		2023-04-15				1.287		23-Mar-23		29-Mar-23				0.028
4	2023-07-05	IHI		2021-01-16							0.99	0.99	#VALUE!			2022-09-28		2022-10-14				0.240		2022-12-14		2023-01-16				1.224		13-Dec-22		19-Dec-22				0.097
5																2022-06-29		2022-07-15				0.240		2022-09-14		2022-10-15				1.224		26-Sep-22		30-Sep-22				0.146
6																2022-03-30		2022-04-14				0.227		2022-06-14		2022-07-15				1.224		09-Jun-22		15-Jun-22				0.064
7																2021-12-30		2022-01-14				0.227		2022-03-14		2022-04-15				1.224		24-Mar-22		30-Mar-22				0.007
8																2021-09-28		2021-10-15				0.227		2021-12-14		2022-01-15				1.164		30-Dec-21		05-Jan-22				0.106
9																2021-06-29		2021-07-15				0.227		2021-09-14		2021-10-15				1.164		24-Sep-21		30-Sep-21				0.039
10																2021-03-30		2021-04-15				0.206		2021-06-14		2021-07-15				1.164		10-Jun-21		16-Jun-21				0.406
11																2020-12-30		2021-01-15				0.206		2021-03-12		2021-04-15				1.164		25-Mar-21		31-Mar-21				0.029
12																2020-09-29		2020-10-15				0.206		2020-12-14		2021-01-15				1.107		14-Dec-20		18-Dec-20				0.539
13																2020-06-29		2020-07-15				0.206		2020-09-14		2020-10-15				1.107		23-Sep-20		29-Sep-20				0.263
14																2020-03-30		2020-04-15				0.188		2020-06-12		2020-07-15				1.107		15-Jun-20		19-Jun-20				0.252
15																2019-12-30		2020-01-15				0.188		2020-03-13		2020-04-15				1.107		25-Mar-20		31-Mar-20				0.028
16																2019-09-27		2019-10-15				0.188		2019-12-13		2020-01-15				1.054		30-Dec-19		06-Jan-20				0.049
17																2019-06-27		2019-07-15				0.188		2019-09-13		2019-10-15				1.054		16-Dec-19		20-Dec-19				0.531
18																2019-03-28		2019-04-15				0.171		2019-06-13		2019-07-15				1.054		24-Sep-19		30-Sep-19				0.251
Sheet1

Cell Formulas
Range	Formula
K2:K4	K2	=SUMIFS(XLOOKUP($B2&"_Div",DivHeader,DivData,,0), XLOOKUP($B2&"_ExDivDate",DivHeader,DivData,,0),">"&$D2, XLOOKUP($B2&"_PayDate",DivHeader,DivData,,0),"<="&$A2)
L2:L4	L2	=SUMIFS(XLOOKUP($B2&"_Div",DivHeader,DivData,,0), XLOOKUP($B2&"_ExDivDate",DivHeader,DivData,,0),">"&$D2, XLOOKUP($B2&"_PayDate",DivHeader,DivData,,0),"<="&$A2)
M2:M4	M2	=SUMIFS(XLOOKUP($B2:B4&"_Div",DivHeader,DivData,,0), XLOOKUP($B2:$B4&"_ExDivDate",DivHeader,DivData,,0),">"&$D2:$D4, XLOOKUP($B2:$B4&"_PayDate",DivHeader,DivData,,0),"<="&$A2:$A4)
V2:V18,AL2:AL18,AD2:AD18	V2	=U2
A2	A2	=TODAY()
A3:A4	A3	=$A$2
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
DivData	=Sheet1!$O$2:$AL$18	M2, K2:L4
DivHeader	=Sheet1!$O$1:$AL$1	M2, K2:L4

 

[Peter_SSs]

See how this goes. I have assumed that the hidden O1 does not begin with "AQN_"

23 07 06.xlsm
	A	B		D							K	L	M			P		R				V		X		Z				AD		AF		AH				AL
1	Date	Stock		Date Purch							NON-ARRAY Gain/Loss	CSE Gain/Loss	SPILL ARRAY Gain/Loss			AQN_ExDivDate		AQN_PayDate				AQN_Div		BCE_ExDivDate		BCE_PayDate				BCE_Div		IHI_ExDivDate		IHI_PayDate				IHI_Div
2	6/07/2023	AQN		1/06/2022							0.864		0.864			30/03/2023		14/04/2023				0.144		14/06/2023		15/07/2023				1.287		7/06/2023		13/06/2023				0.071
3	6/07/2023	BCE		15/09/2021							7.347		7.347			29/12/2022		13/01/2023				0.24		14/03/2023		15/04/2023				1.287		23/03/2023		29/03/2023				0.028
4	6/07/2023	IHI		16/01/2021							0.993		0.993			28/09/2022		14/10/2022				0.24		14/12/2022		16/01/2023				1.224		13/12/2022		19/12/2022				0.097
5																29/06/2022		15/07/2022				0.24		14/09/2022		15/10/2022				1.224		26/09/2022		30/09/2022				0.146
6																30/03/2022		14/04/2022				0.227		14/06/2022		15/07/2022				1.224		9/06/2022		15/06/2022				0.064
7																30/12/2021		14/01/2022				0.227		14/03/2022		15/04/2022				1.224		24/03/2022		30/03/2022				0.007
8																28/09/2021		15/10/2021				0.227		14/12/2021		15/01/2022				1.164		30/12/2021		5/01/2022				0.106
9																29/06/2021		15/07/2021				0.227		14/09/2021		15/10/2021				1.164		24/09/2021		30/09/2021				0.039
10																30/03/2021		15/04/2021				0.206		14/06/2021		15/07/2021				1.164		10/06/2021		16/06/2021				0.406
11																30/12/2020		15/01/2021				0.206		12/03/2021		15/04/2021				1.164		25/03/2021		31/03/2021				0.029
12																29/09/2020		15/10/2020				0.206		14/12/2020		15/01/2021				1.107		14/12/2020		18/12/2020				0.539
13																29/06/2020		15/07/2020				0.206		14/09/2020		15/10/2020				1.107		23/09/2020		29/09/2020				0.263
14																30/03/2020		15/04/2020				0.188		12/06/2020		15/07/2020				1.107		15/06/2020		19/06/2020				0.252
15																30/12/2019		15/01/2020				0.188		13/03/2020		15/04/2020				1.107		25/03/2020		31/03/2020				0.028
16																27/09/2019		15/10/2019				0.188		13/12/2019		15/01/2020				1.054		30/12/2019		6/01/2020				0.049
17																27/06/2019		15/07/2019				0.188		13/09/2019		15/10/2019				1.054		16/12/2019		20/12/2019				0.531
18																28/03/2019		15/04/2019				0.171		13/06/2019		15/07/2019				1.054		24/09/2019		30/09/2019				0.251
DaveyP

Cell Formulas
Range	Formula
M2:M4	M2	=BYROW(A2:D4,LAMBDA(rw,LET(fc,MATCH(INDEX(rw,2)&"_*",DivHeader,0),SUMIFS(INDEX(DivData,0,fc+6),INDEX(DivData,0,fc),">"&INDEX(rw,4),INDEX(DivData,0,fc+2),"<="&INDEX(rw,1)))))
A2	A2	=TODAY()
A3:A4	A3	=$A$2
K2:K4	K2	=SUMIFS(XLOOKUP($B2&"_Div",DivHeader,DivData,,0), XLOOKUP($B2&"_ExDivDate",DivHeader,DivData,,0),">"&$D2, XLOOKUP($B2&"_PayDate",DivHeader,DivData,,0),"<="&$A2)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
DivData	=DaveyP!$O$2:$AL$18	M2, K2:K4
DivHeader	=DaveyP!$O$1:$AL$1	M2, K2:K4

 

[DaveyP]

Thank you for the quick response. Unfortunately my Excel 2021 doesn't have the newer functions such as BYROW LAMBDA etc. I have FILTER, LET, MMULT, UNIQUE and the X's ie XLOOKUP, XMATCH but not the newest features available with the 365 subscription version. Is there a work around using ROW or ROWS?

 

[Peter_SSs]

Unfortunately my Excel 2021 doesn't have the newer functions such as BYROW LAMBDA etc.

Sorry, I keep forgetting about exactly what is in 2021 (as I don't have it)
I'm not sure that you will be able to get spilled results for that circumstance. (Happy for somebody to prove me wrong though )