Tracking Renewal Increases Formula

tim.curtin

Hello!

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

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.