I have below formula running correctly. However, when I add new records under row 28. The formula will not work until I update the formula to include the new records. How can I modify the formula to work automatically with new records added? Thanks!

=SUMIFS(I2:I28,A2:A28,">="&G35,A2:A28,"<="&H35)

If you want to be able to 'add rows' beneath a range you have already determined, you could:

* add the row(s) "inside" the current range instead of after the bottom of the range

* make your range large enough to encompass any added row(s) (e.g., instead of A2:A28, how 'bout A2:A200?)

Thanks for your suggestion. It would work but brings another issue. I have some calculated results on the bottom of record rows. Adding a few rows would not affect the printed report but adding 100 rows will affect the printed results with blank in the central rows. If no other way, probably I have to move the calculated results from the bottom to the top.

hello, Ray, i wonder if either of the following links will be helpful:

https://www.excel-easy.com/examples/dynamic-named-range.html

https://exceljet.net/lessons/how-to-create-a-dynamic-named-range-with-a-table

i googled "Excel flexible range or table", and those are just two of the hits i got. good luck with it. :)

hth

tina

Hi Tina, many thanks for your info abt dynamic named range. It is useful for this application!

oh, good, hon, glad it's useful. :) i did some stuff in Excel about six years ago, and though i got back to Access as fast as i could, a few things stuck in my mind, but not details. tina

There is another, rather rogue solution, and that is to pro-grammatically create the Formula in the SelectionChange() Event of your Worksheet. This requires no modification of the existing Formula in extending the Rows reference, is dynamic, and no insertion of Rows within the existing Range. Let's assume that you want this Formula in Range("O11") of your Worksheet and you want the capability of adding X number of Rows without manually adjusting the Formula or anything else for that matter. The Code is posted below, and it appears to work very well in limited trials:

CODE

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim lngLastRowWithData

lngLastRowWithData = Range("A" & Rows.Count).End(xlUp).Row

Range("O11").Formula = "=SUMIFS(I2:I" & CStr(lngLastRowWithData) & ",A2:A" & CStr(lngLastRowWithData) & "," & _

Chr$(34) & ">=" & Chr$(34) & "&G35,A2:A" & CStr(lngLastRowWithData) & "," & _

Chr$(34) & "<=" & Chr$(34) & "&H35)"

End Sub

Dim lngLastRowWithData

lngLastRowWithData = Range("A" & Rows.Count).End(xlUp).Row

Range("O11").Formula = "=SUMIFS(I2:I" & CStr(lngLastRowWithData) & ",A2:A" & CStr(lngLastRowWithData) & "," & _

Chr$(34) & ">=" & Chr$(34) & "&G35,A2:A" & CStr(lngLastRowWithData) & "," & _

Chr$(34) & "<=" & Chr$(34) & "&H35)"

End Sub