Formula for tiered pricing

[BarbSs]

I cannot find a formula that works for tiered pricing. Please help.

1-6000 gals is priced at $3.55 per 1000 gals ($0.00355 per gallon)
6000-25000 gals is priced at $3.95 per 1000 gals ($0.00395 per gallon)
25000 gals and up is priced at $4.40 per 1000 gals ($0.00440 per gallon)

I want to set up a formula that returns the total price when I put in the total number of gallons. So if the total of gallons is 33,144 the formula should give me a total cost based on all those tiers.

I tried using sumproduct but can't get it to work. All of the formulas I have found have price breaks. This formula needs to calculate price increase.

Any ideas? Thanks!

 

[sheetspread]

Try this:

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

 

[sheetspread]

Excel 2010
	A	B
1	33144	132.1836
Sheet7

Cell Formulas
Range	Formula
B1	=MIN(A1,6000)*0.00355 +MEDIAN(0,A1-6000,19000)*0.00395 +MAX(A1-25000,0)*0.0044

 

[MARK858]

Or

Excel Workbook
	A	B	C	D
1	33144			132.1836
Sheet2

 

[Dave Patton]

Excel 2010
	B	C	D	E	F	G
1	Gallons	Amount		Brackets	Rates	Delta
2	33,144.00	132.18		0	0.003550	0.003550
3				6,000	0.003950	0.000400
4				25,000	0.004400	0.000450
5
6	T10_1807a	2d
2d

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(--(B2>rB),B2-rB,rD)
F2	=3.55/1000
G2	=F2-N(F1)

Named Ranges
Name	Refers To	Cells
rB	='2d'!$E$2:$E$4
rD	='2d'!$G$2:$G$4