Array that can pull data from different ranges

[xcl1997]

{=SUM(INDIRECT("'sheet1'!"&SUBSTITUTE(ADDRESS(1,XMATCH(C$2,INDIRECT("'sheet1'!A1:"&SUBSTITUTE(ADDRESS(1,O1+16,4),"1","")&ROWS($M$1:$M$1)),0),4),"1","")&XMATCH($B3,INDIRECT("'sheet1'!$m$1:$m"&M1),0)):INDIRECT("'sheet1'!"&SUBSTITUTE(ADDRESS(1,XMATCH(C$2,INDIRECT("'sheet1'!A1:"&SUBSTITUTE(ADDRESS(1,O1+16,4),"1","")&ROWS($M$1:$M$1)),0),4),"1","")&XMATCH($B3+1,INDIRECT("'sheet1'!$m$1:$m"&M1),0)-1))}

The formula above is in C3 and is only the results part of the actual formula in the spreadsheet. The actual formula has to conditions - one for if the date in row B equals the max date in row M, and another for when the row B date is less than the row M max. To save space, I've only posted the result for when row B is less than row K.

By using "indirect", the Table 1 C3 formula can be copied and pasted to the rest of the cells in the table (without having to make an adjustment for each row because of the different cell ranges for dates in Table 2).

Is there a way to adjust the C3 formula so that it acts like an array that automatically calculates the rest of the cells going either across each row (so there is only a need to copy and paste down column C) or down column C (so there is only a need to copy and paste going across row 3)?

 

[xcl1997]

Should mention - Table 1 title says the total sum is for column AA thru AT food. It should say for columns P thru W.