Formula for Tiered Pricing

Hello,

I was hoping someone could assist me with the following problem. I am trying to work on a formula that will assist me with billing for tiered pricing. The tiers and pricing are as followed -

up to 10,000 - 0.20 cents per record
10,001 - 50,000 - 0.10 cents per record
50,001-100,000 - 0.05 cents per record
100,001 - 500,000 - 0.03 cents per record
500,001-1,000,000 - 0.02 cents per record

I have been trying to utilize the sum product formula which is as followed - =SUMPRODUCT(--(O2>{0,10000,40000,50000,400000,500000}),--(O2-{0,10000,40000,50000,400000,500000}),{0,0.2,-0.1,-0.05,-0.03,-0.02})-P2

however I am off by 1k.

I have also attached the spreadsheet I am working on which gives the amount of records per month. Any help will be appreciated

Hello,

The formula should return the amount to be billed per quarter in reference to the usage within the quarter. The usage however determines the rate billed and it is cumulative. Please use my spreadsheet attached as a reference.

I figured it out! If anyone ever comes across this thread see the below link

http://www.mcgimpsey.com/excel/variablerate.html