I'm working to write a formula to calculate total cost for using a vendor's equipment when the cost of using the equipment increases at different rates based on the number of days used.

ex. the equipment was used for a total of 7 days, the equipment vendor charges $95/day for 1-4 days, the rate then increases to $140/day for 5-8 days, and increases again to $175 for any day the equipment is kept past 9 days.

In this scenario the correct total cost for using the equipment is $800

There are multiple vendors, all having different rate schedules. I'm currently calculating using an offset formula, and have all vendors and rates in a chart which pulls the rate based on vendor name and total number of days. The rate populates and multiples by total number of days, however this is overstating the total cost. If the number of days falls into a higher tier, the current formula takes the rate times the number of days, not taking into consideration the lesser charge for the first tier of days.

Existing formula would calc the total as 7*140 = $980

I'm assuming this would still use offset in some manner, but can't quite get there.

Thanks in advance any advice you can lend!