Need formula range to automatically expand when a new row is added to table

[Mike423]

I'm currently using the below formula to sum individual payroll values from a large table into a smaller, summary table.

=IF(D2="YTD",SUMPRODUCT((G4:BQ13)*(G3:BQ3=A6)*(F4:F13=B2)),SUMPRODUCT((G4:BQ13)*(F4:F13=B2)*(G2:BQ2=D2)*(G3:BQ3=A6)))

My question is, how can I modify this formula, so that when I drag the bottom row of the table down (it is actually a table, not just a range) to add a new row, the ranges in the formula adjust along with it to include any data entered into the new row? Picture is attached.

Thank you.

 

[KRice]

I'll post a revision soon.

 

[KRice]

Okay, give this a try. I'm still guessing about your column heading names, but I see that you've used them in post #25. I still do not believe there can be duplicate names, so I'm assuming that the column headers closely resemble the queried week numbers. Then the work-around is to create a new modified column heading array (called chm in the formula), where I've taken the length of the text string in $D$3 and then use that many characters from the left of each column heading. This should result in all columns for a chosen week group to be identified by the formula.

=LET(tbl,Weekly_Payroll,
ci,FILTER(SEQUENCE(,COLUMNS(tbl)),INDEX(tbl,1,)=$A7),
ri,FILTER(SEQUENCE(ROWS(tbl)),INDEX(tbl,,1)=$B$3),
chm,LEFT(Weekly_Payroll[#Headers],LEN($D$3)),
SUM(INDEX(tbl,ri,IF($D$3="YTD",ci,MATCH(1,(INDEX(tbl,1,)=$A7)*(chm=$D$3),0)))))