Help with SUMPRODUCT for tiered pricing table

[emoody]

Hello,

I currently have a pricing table with the following unit prices:

0-4999 costs $0.6850
5000-9999 costs $0.650
10000-24999 costs $0.6150

And so on and so forth.

The tiered pricing means that anything up to 4999 units is priced at $0.6850 and anything over that is priced at that tier's corresponding amount.

I have seen a lot of examples that use SUMPRODUCT, but it is not delivering on the above requirement - it will calculate correctly but just for whichever tier it falls into, not the multiple tier scenario.

Would anyone be able to take a stab at this or give me your thoughts?

Min	Max	Price
0	4999	0.685
5000	9999	0.645
10000	24999	0.615
25000	49999	0.59
50000	99999	0.57
100000	249999	0.555

 

[kweaver]

Welcome to Mr. Excel,

What exactly do you want to find? The Price based on a given number somewhere in the sheet?
Is 6000, for example, supposed to return 0.645?

 

[emoody]

Welcome to Mr. Excel,

What exactly do you want to find? The Price based on a given number somewhere in the sheet?
Is 6000, for example, supposed to return 0.645?

Sorry, I should have taken it further. I am looking for the total cost based on a cell elsewhere in the sheet.

For 6000 units I would expect it to return a total of $4069.96, where: (4999*0.685 = $3424.32) and (1001*0.645 = $645.65)

 

[kweaver]

I haven't turned this into a single formula, but is this at least calculating what you expect?

Tiered fee calculation.xlsx
	A	B	C	D	E
1		Tier Minimum	Tier Maximum	Rate	Price
2		0	4999	0.685	$3,424.32
3		5000	9999	0.645	$3,225.00
4		10000	24999	0.615	$615.62
5		25000	49999	0.59	$0.00
6		50000	99999	0.57	$0.00
7		100000	249999	0.555	$0.00
8
9	Amount	11000
10	Result	$7,264.93
Sheet2

Cell Formulas
Range	Formula
E2	E2	=MAX(0,MIN(C2,$B$9)*D2)
E3:E7	E3	=MAX(0,MIN(C3-C2,$B$9-C2)*D3)
B10	B10	=SUM(E2:E7)

 

[AhoyNC]

This seems to work using SUMPRODUCT in one cell.

Book1
	A	B	C	D	E	F	G
1		Tier Minimum	Tier Maximum	Rate	Price		Rate Difference
2		0	4999	0.685	3424.315		0.685
3		5000	9999	0.645	645.645		-0.04
4		10000	24999	0.615	0		-0.03
5		25000	49999	0.59	0		-0.025
6		50000	99999	0.57	0		-0.02
7		100000	249999	0.555	0		-0.015
8
9	Amount	6000
10	Result	4069.96
11
12	With SUMPRODUCT	4069.96
Sheet1

Cell Formulas
Range	Formula
E2	E2	=MAX(0,MIN(C2,$B$9)*D2)
E3:E7	E3	=MAX(0,MIN(C3-C2,$B$9-C2)*D3)
G3:G7	G3	=D3-D2
B10	B10	=SUM(E2:E7)
B12	B12	=SUMPRODUCT(--(B9>{0,4999,9999,24999,49999,99999}),(B9-{0,4999,9999,24999,49999,99999}),{0.685,-0.04,-0.03,-0.025,-0.02,-0.015})