# Formula for calculating escalating values

✭✭✭
edited 12/08/23

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.

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭

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!