adjust formula so not need updating

Question

adjust formula so not need updating

Megan F 120

I am using the formula below that is working fine, but it requires me to keep updating the number of rows to include the cells with data. The number of rows will always be changing - sometimes more, sometimes less. Right now, there are 14 rows with data. I would like to update this formula to just check all of the rows in the columns (or if that's too many then rows 3-50 at least). This way we don't have to keep manually updating the formula. I have tried using Google AI to get a variation of this formula that does this, but it just keeps giving me either a formula pop up error message or the #spill error. I probably tried 30 variations. Is this possible?

=BYROW(A36:A39,LAMBDA(z,LET(a,1*(REGEXEXTRACT(TEXTSPLIT(z, "-"), "\d+", 1) & "/" & A$34 & "/2026"),SUM(($G$3:$G$14>=INDEX(a,1,1))($G$3:$G$14<=INDEX(a,1,2))($H$3:$H$14+$I$3:$I$14)))))

here is a link to an example file:

https://storage.to/qNmMUOE4z

I asked a question to get this formula initially and there is a more detailed explanation of what I'm doing there:

https://learn.microsoft.com/en-us/answers/questions/6022552/totals-for-specific-dates-formula

0 comments

Answer accepted by question author

Hani-Ng 2,835 Independent Advisor

Hi Megan F

You may want to review AI's recommendation first, as it addresses one of the most common approaches for handling a changing number of source rows without updating the formula each time.

Looking back at your original requirement, if that approach does not work in your workbook, it would be helpful to know exactly which modification is causing the issue. For example:

Does the formula work correctly when you extend the ranges beyond row 14 (such as to row 50)?
Do you only encounter the error when attempting to use a fully dynamic range?
Are you receiving a formula syntax error or specifically a #SPILL! error?

If the error is #SPILL!, the cause may not be the range references themselves. Dynamic array formulas can also return #SPILL! when the output range is blocked by existing data, merged cells, or other worksheet constraints.

If possible, please share the exact formula you are currently testing and a screenshot of the error message. Since your current formula was originally developed in the thread you referenced, those details may help forum members determine whether the issue is with the updated range references or another part of the formula.

I hope this information helps and look forward to your response.

0 comments

3 additional answers

Your answer

Answer 1

You can make the formula self adjusting by inserting some additional computations near the beginning of Ashish's solution. The approach is to identify the last row in column G and then set one range equal to H3:H_row and another to I3:I_row

=BYROW(A36:A39,LAMBDA(z,LET(p,CELL("address",XLOOKUP(TRUE, G:G<>"", G:G, "", 0, -1)),q,TEXTSPLIT(p,"$"),r,INDEX(q,1,3),h,H3:INDEX(H:H,r),i,I3:INDEX(I:I,r),a,1*(REGEXEXTRACT(TEXTSPLIT(z, "-"), "\d+", 1) & "/" & A$34 & "/2026"),SUM(($G$3:$G$14>=INDEX(a,1,1))*($G$3:$G$14<=INDEX(a,1,2))*($H$3:$H$14+$I$3:$I$14)))))

In the LET function

Assign the address of the last cell ($G$14 in your sample) to the variable p.
Split the address into three parts in the variable q
Extract the row number into variable r.
Use r to define the H range and the I range.
Everything from the variable a on is unchanged from your current formula.

As the number of rows of data changes, the H and I ranges will adjust automatically.

Answer 2

Megan F 120

Changing the 14 to a 50 created a #VALUE error before, but I just did it and it worked. I must've messed something else up when I was playing with it. The formula you provided gives me the #VALUE error, but I guess I'm all set since the formula updated properly now. Thanks!

Hani-Ng 2,835 Reputation points Independent Advisor

2026-10-22T17:43:45.41+00:00

Hi Megan F

Glad to hear it's working now! It sounds like something else may have been causing the #VALUE! error earlier.

If extending the range to row 50 is calculating correctly, then you should be all set for now. Thanks for coming back with the update, and it may help others running into a similar issue.

Have a great day!

Answer 3

Use a dynamic source range instead of fixed row 14 references.

If the data can grow up to row 50, replace $G$3:$G$14, $H$3:$H$14, and $I$3:$I$14 with row 50 versions:

=BYROW(A36:A39,LAMBDA(z,LET(a,1*(REGEXEXTRACT(TEXTSPLIT(z,"-"),"\d+",1)&"/"&A$34&"/2026"),SUM(($G$3:$G$50>=INDEX(a,1,1))*($G$3:$G$50<=INDEX(a,1,2))*($H$3:$H$50+$I$3:$I$50)))))

If the source data is expected to keep changing, the more durable approach is to convert the source range into an Excel Table and use structured references. Excel Tables automatically expand when new rows are added, so formulas do not need manual range updates.

A practical pattern is:

Select the source data range.
Convert it to a Table.
Replace fixed ranges with table columns instead of G3:G14, H3:H14, and I3:I14.

That approach is recommended when source data grows or shrinks regularly.

Also, if a formula returns multiple values, make sure it is entered in regular cells with enough empty space below for the results to spill. Dynamic array formulas will not work inside Excel table cells.