Formula for calculating escalating values

Options
Kim Stephens
Kim Stephens ✭✭✭
edited 12/08/23 in Formulas and Functions

I am completely stumped with how to start this formula. It is in reference to billing false alarms occurring in a month. The first 1 is $50, a 2nd is $100, after 3, it jumps to $200 each.

If there were 5 false alarms in the month, the total would be $750 (1@$50, 1@$100, 3@$200).

Any help would be appreciated.

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Kim Stephens

    Try this in your billable column. You will need to edit the formula to match your actual column names. I am assuming you will have to gather the data from dates, vs you already have the counts in a single field. If you can share a screenshot of your data structure, I can make the formula specific to your sheet. If you want to make the data for a specific month (I'm assuming monthly billing?, you can plug in 12, for instance, in place of MONTH(TODAY()). If you have multiple years in the same sheet, we will have to correct for that

    =IF(COUNTIFS(Date:Date, MONTH(@cell)=MONTH(TODAY()), Client:Client, Client@row)=1, 50, =IF(COUNTIFS(Date:Date, MONTH(@cell)=MONTH(TODAY()), Client:Client, Client@row)=2, 150, =IF(COUNTIFS(Date:Date, MONTH(@cell)=MONTH(TODAY()), Client:Client, Client@row)>=3, (COUNTIFS(Date:Date, MONTH(@cell)=MONTH(TODAY()), Client:Client, Client@row)-2)*200+150)))

    If you add the screenshot and/or have questions, reach out to me with an @mention

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Kim Stephens

    Try this in your billable column. You will need to edit the formula to match your actual column names. I am assuming you will have to gather the data from dates, vs you already have the counts in a single field. If you can share a screenshot of your data structure, I can make the formula specific to your sheet. If you want to make the data for a specific month (I'm assuming monthly billing?, you can plug in 12, for instance, in place of MONTH(TODAY()). If you have multiple years in the same sheet, we will have to correct for that

    =IF(COUNTIFS(Date:Date, MONTH(@cell)=MONTH(TODAY()), Client:Client, Client@row)=1, 50, =IF(COUNTIFS(Date:Date, MONTH(@cell)=MONTH(TODAY()), Client:Client, Client@row)=2, 150, =IF(COUNTIFS(Date:Date, MONTH(@cell)=MONTH(TODAY()), Client:Client, Client@row)>=3, (COUNTIFS(Date:Date, MONTH(@cell)=MONTH(TODAY()), Client:Client, Client@row)-2)*200+150)))

    If you add the screenshot and/or have questions, reach out to me with an @mention

    Kelly

  • Kim Stephens
    Options

    Thank you so much- that worked great! I

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!