Formula Needed to Calc Total Cost for Using Equipment when Rate Increases per Days Used

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!

Try this for a given vendor:

it use the following

=SUMPRODUCT(($C$2>=$A$2:$A$4)*($C$2-$A$2:$A$4),$B$2:$B4)

The table uses the increments between rates: see notes in file.

So I think you will need a table per Vendor.

I figured it out!

I used the Sum and Offset formulas to work through. Here's an example formula in case anyone needs help working through a
similar issue.


=SUM(OFFSET(offset!$A$1,AU2,AV2,-1*AT3,1)

Offset!$A$1 = my data table on a sheet named "offset"
AU2 = the number of cells down of the starting cell (A1) within my offset table
AV2 = the number of cells to the right of the starting cell (A1) within my offset table
-1*AT3 = the number of rows in an upward direction to sum from the cell where AU2 and AV2 met
1 = the number of columns to sum from the cell where AU2 and AV2 met

Thanks for your help!