Tiered Pricing SumIF Help

[lewdow]

Hi - I am trying to calculate the overall price based on a tiers of discounts:

[TABLE="width: 383"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Lower Band[/TD]
[TD]Upper Band[/TD]
[TD]List Price[/TD]
[TD]Discount%[/TD]
[TD]Discounted Price[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]7,500[/TD]
[TD="align: right"]£10.00[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]£10.00[/TD]
[/TR]
[TR]
[TD="align: right"]7,501[/TD]
[TD="align: right"]15,000[/TD]
[TD="align: right"]£10.00[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]£9.70[/TD]
[/TR]
[TR]
[TD="align: right"]15,001[/TD]
[TD="align: right"]25,000[/TD]
[TD="align: right"]£10.00[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]£9.40[/TD]
[/TR]
[TR]
[TD="align: right"]25,001[/TD]
[TD="align: right"]35,000[/TD]
[TD="align: right"]£10.00[/TD]
[TD="align: right"]9%[/TD]
[TD="align: right"]£9.10[/TD]
[/TR]
[TR]
[TD="align: right"]35,001[/TD]
[TD="align: right"]40,000[/TD]
[TD="align: right"]£10.00[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]£8.80[/TD]
[/TR]
[TR]
[TD="align: right"]40,001[/TD]
[TD] 50000+[/TD]
[TD="align: right"]£10.00[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]£8.50[/TD]
[/TR]
</tbody>[/TABLE]

This would be a tiered discount model - so the first 7,500 are sold at list price, the 7501st unit up to 15,000 are at 3% discount etc. It's not the case that buying 14,000 units would see all 14,000 at a 3% discount, the first 7500 would still be at 0% discount.

I've tried many times to resolve this using SUMPRODUCT from other examples and just can't get it to work! Anyone that can help would be much appreciated.

Thanks

L

 

[sheetspread]

Look here:

https://www.mrexcel.com/forum/excel-questions/912202-calculating-commission.html