Tracking Renewal Increases Formula


I need some help putting together a formula to track year to date renewal increases.

My criteria is

Underwriting Status needs to be set at "Completed Increase" and i need the sum of all the rates for increases to insurance premiums. I need to total for each row and have a YTD total at the top.

For my row formula I am doing =SUMIF([Basic Life Rate]@row:[Grp LTD Rate]@row, [Underwriting Status]@row = "Completed Increase") but I am getting a return of 0 with data in the row. Any suggestions?


  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @tim.curtin,

    For your row sum:

    =IF([Underwriting Status]@row = "Completed Increase", SUM([Basic Life Rate]@row:[Grp LTD Rate]@row), "")

    For the total of these, you can then just SUM that column:

    =SUM([Completed Increase SUM]:[Completed Increase SUM])

    Your SUMIF syntax is a bit wrong, but even correcting it I couldn't get the function to handle multiple columns - you could do them separately and add together, but I've no idea how many you have.

    As an example, if you've only those 2 columns:

    =SUMIF([Underwriting Status]@row, "Completed Increase", [Basic Life Rate]@row) + SUMIF([Underwriting Status]@row, "Completed Increase", [Grp LTD Rate]@row)

    If there are others, then you would add those on.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!