Sumproduct?

[bdbilotta]

I am trying to dynamically fill in C3:J3 on the first sheet by referencing the sum of AD21:AL5000 where each column on the second sheet (SG01_024080140324) corresponds to the headers in row 2 of the first sheet (4ContTtls), and where the date show column AB of the second sheet is less than or equal to the date shown in B3 of the first sheet..

There will be many sheets added and taken away from this project. I will be using INDIRECT to dynamically point to additional sheets once I figure out (or you all figure out ) how fill this in. I am assuming it is SUMPRODUCT, but I can't seem to get there. I keep getting #NA, or #VALUE.
Im posting a mini sheet of Sheet 1, and an image of sheet 2.
Any help would be welcome.
Thank you,
BB

RangeSystem.xlsx
	A	B	C	D	E	F	G	H	I	J
2	SG01_04,20,10,-3	Date	75	100	125	150	175	200	300	400
3	SG01_024080140324	9/8/2023	#N/A
4ContTtls

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT((SG01_024080140324!AB21:SG01_024080140324!AB23<='4ContTtls'!B3)*(SG01_024080140324!AD21:SG01_024080140324!AL20='4ContTtls'!C2)*SG01_024080140324!AD21:SG01_024080140324!AL23)

 

[bdbilotta]

I am trying to dynamically fill in C3:J3 on the first sheet by referencing the sum of AD21:AL5000 where each column on the second sheet (SG01_024080140324) corresponds to the headers in row 2 of the first sheet (4ContTtls), and where the date show column AB of the second sheet is less than or equal to the date shown in B3 of the first sheet..

There will be many sheets added and taken away from this project. I will be using INDIRECT to dynamically point to additional sheets once I figure out (or you all figure out ) how fill this in. I am assuming it is SUMPRODUCT, but I can't seem to get there. I keep getting #NA, or #VALUE.
Im posting a mini sheet of Sheet 1, and an image of sheet 2.
Any help would be welcome.
Thank you,
BB

RangeSystem.xlsx
	A	B	C	D	E	F	G	H	I	J
2	SG01_04,20,10,-3	Date	75	100	125	150	175	200	300	400
3	SG01_024080140324	9/8/2023	#N/A
4ContTtls

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT((SG01_024080140324!AB21:SG01_024080140324!AB23<='4ContTtls'!B3)*(SG01_024080140324!AD21:SG01_024080140324!AL20='4ContTtls'!C2)*SG01_024080140324!AD21:SG01_024080140324!AL23)

After posting I discovered one of my problems that does not appear in the image of Sheet 2 I posted and that is that in some cells I'm using "" to achieve blanks. This was done to get correct counts later, which can be done by filtering out the 0.00 results in those cells.