Formula for calculating escalating values

Kim Stephens

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.

Kelly Moore

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

Kelly Moore

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

Thank you so much- that worked great! I