VBA Sumif with Dynamic Range

Each loop of the code will change the number of rows so it has to be dynamic

What does that mean? You haven't shown a loop.

So, column R is a different formula to all the others? Or does it go in every cell in column R from row 2 down to the final row (which you have already calculated)

And is this a different worksheet to the rest of the code?

ActiveWorkbook.Worksheets("Closed Deals").Range("A" & LR + 1).EntireRow.Font.Bold = True
ActiveWorkbook.Worksheets("Closed Deals").Range("A2:AE" & LR + 1).Borders(xlEdgeBottom).LineStyle = xlDouble

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Ok, got that, thought that might be it. Back shortly.

Try this:

Option Explicit

Sub sDynamicRange_TMS()

Dim FinalRow As Long

With ActiveWorkbook.Worksheets("Closed Deals")
    FinalRow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range(.Cells(FinalRow + 1, 15), .Cells(FinalRow + 1, 24)).Formula = "=sum(O2:O" & FinalRow & ")"
    .Cells(FinalRow + 1, 30).Formula = "=sum(AD2:AD" & FinalRow & ")"

    .Range("A" & FinalRow + 1).EntireRow.Font.Bold = True
    .Range("A2:AE" & FinalRow + 1).Borders(xlEdgeBottom).LineStyle = xlDouble

    .Cells(FinalRow + 3, 17).Value = "Total Quota Credit Less Renewals"
    .Cells(FinalRow + 3, 17).WrapText = True
    .Cells(FinalRow + 3, 17).Font.Bold = True
    .Cells(FinalRow + 3, 18).Formula = "=SUMIF($N2:$N" & FinalRow & ",""<>Renewal"",$R2:$R" & FinalRow & ")"
End With

End Sub