Dynamic Tiered Pricing Table

[mtardif]

I'm new to the community and was trying to find a solution for a tiered pricing module. I see a few solutions but they tend to be hard-coded with the tiers and rates directly in the formula. I'm curious if it can be dynamic where I can change the rates, or tiers in the cells without having to redo the formula. When I change a number to reference the CELL in some of the examples they no longer work.

It looks like SUMPRODUCT is the right answer but I've seen a couple using MIN and MAX but those seem rather convoluted.

One solution was close but didn't work for quantities over the max number. In other words any number over "26" produced the same result.

I've attached the worksheet with some example results (calculated manually) and was hoping someone would be able to help us out. It seems the formula works based on the difference so I added that column as well.

The Tiers:
First site = $2,500
2 through 5 = 1,000
6 through 10 = 750
11 through 25 = 500
26 + = 250

The reason we want it dynamic is we may want to change the second tier from $1,000 to $1,250 (or 850) just to see how that impacts a deal.

Any help would be appreciated.

Regards,

Mike

[CK76]

Since you have Office 365. Provided that you have the latest update applied...

=SUM(LOOKUP(SEQUENCE(C10,,1,1),A4:A8,C4:C8))

If you need it to be compatible with older versions...
=SUM(LOOKUP(ROW(A1:INDEX(A:A,C10)),A4:A8,C4:C8))

Confirmed as array (CTRL + SHIFT + ENTER).

[mtardif]

Thanks CK76! This works great.

I will now try to figure out how to mark this as solved and give you the reputation boost.

Thanks again.

[CK76]

You are welcome and thanks for the rep