SUMPRODUCT assistance

[excel_caveman]

Hello, I have found a couple of threads that look like they would help, but they haven't quite got the right answer. I am trying to use SUMPRODUCT (can't use macros etc.) to calculate the price of a contract based on the volume of units sold. The unit price is determined by a cumulative tiered pricing model. As an example, someone purchasing 800 units should be charged (250 x $20) + (250 x $17.5) + (250 x $15) + (50 x $12.5)= $13,750. Table starts in cell A1, so B2 in the formula below is referencing the 250 in the To column, and B11 is where I am entering the volume to be caluclated.

From	To	Unit price
0	250	$20.0
251	500	$17.5
501	750	$15.0
751	1,000	$12.5
1,001	1,500	$10.0
1,501	2,000	$7.5
2,001	2,500	$5.0
2,501	100,000	$2.5

I have used the following, but it does not seem to be working =SUMPRODUCT((B11>{0;B2;B3;B4;B5;B6;B7;B8;B9})*(B11-{0;B2;B3;B4;B5;B6;B7;B8;B9})*{0;C2;C3;C4;C5;C6;C7;C8;C9})

Thanks in advance

 

[JamesCanale]

MrExcelPlayground16.xlsx
	A	B	C
1	From	To	Unit price
2	1	250	$20.00
3	251	500	$17.50
4	501	750	$15.00
5	751	1,000	$12.50
6	1,001	1,500	$10.00
7	1,501	2,000	$7.50
8	2,001	2,500	$5.00
9	2,501	100,000	$2.50
10
11		800
12
13		13750
Sheet28

Cell Formulas
Range	Formula
B13	B13	=SUMPRODUCT(--(B11>B2:B9),C2:C9*(1+B2:B9-A2:A9))+SUMPRODUCT((B11>=A2:A9)*(B11<=B2:B9),C2:C9*(1+B11-A2:A9))

 

[excel_caveman]

@JamesCanale you are a legend!!! This has been bugging me for hours, and you cracked it for me in 30 minutes.

Thank you very much, much appreciated.

 

[JamesCanale]

Note that I changed A2 to 1 from 0.

 

[excel_caveman]

Note that I changed A2 to 1 from 0.

Gotcha, thanks.

 

[Dave Patton]

With Excel 365, you can use Sum.
I named the bracket information aB and the rate differential aR see the second example

Commissions 2023.xlsm
	A	B	C	D
1	Brackets	Unit Price
2	0	20.00
3	250	17.50
4	500	15.00
5	750	12.50
6	1000	10.00
7	1500	7.50
8	2000	5.00
9	2500	2.50
10
11		800	13,750.00	13,750.00
12
1f

Cell Formulas
Range	Formula
C11	C11	=SUM((B11>AB)*(B11-AB)*aR)
D11	D11	=SUMPRODUCT(--(B11>AB),(B11-AB),aR)

Commissions 2023.xlsm
	A	B	C	D	E	F
1	Brackets	Unit Price	Differential
2	0	20.00	20.00
3	250	17.50	-2.50
4	500	15.00	-2.50
5	750	12.50	-2.50
6	1000	10.00	-2.50
7	1500	7.50	-2.50
8	2000	5.00	-2.50
9	2500	2.50	-2.50
10
11		800	13,750.00	13,750.00	13,750.00	13,750.00
12
1f

Cell Formulas
Range	Formula
C2:C9	C2	=B2-N(B1)
C11	C11	=SUM((B11>AB)*(B11-AB)*aR)
D11	D11	=SUMPRODUCT(--(B11>A2:A9),(B11-A2:A9),C2:C9)
E11	E11	=SUM((B11>A2:A9)*(B11-A2:A9)*C2:C9)
F11	F11	=SUMPRODUCT(--(B11>A2:A9),(B11-A2:A9),C2:C9)

 

[excel_caveman]

With Excel 365, you can use Sum.
I named the bracket information aB and the rate differential aR see the second example

Commissions 2023.xlsm
	A	B	C	D
1	Brackets	Unit Price
2	0	20.00
3	250	17.50
4	500	15.00
5	750	12.50
6	1000	10.00
7	1500	7.50
8	2000	5.00
9	2500	2.50
10
11		800	13,750.00	13,750.00
12
1f

Cell Formulas
Range	Formula
C11	C11	=SUM((B11>AB)*(B11-AB)*aR)
D11	D11	=SUMPRODUCT(--(B11>AB),(B11-AB),aR)

Commissions 2023.xlsm
	A	B	C	D	E	F
1	Brackets	Unit Price	Differential
2	0	20.00	20.00
3	250	17.50	-2.50
4	500	15.00	-2.50
5	750	12.50	-2.50
6	1000	10.00	-2.50
7	1500	7.50	-2.50
8	2000	5.00	-2.50
9	2500	2.50	-2.50
10
11		800	13,750.00	13,750.00	13,750.00	13,750.00
12
1f

Cell Formulas
Range	Formula
C2:C9	C2	=B2-N(B1)
C11	C11	=SUM((B11>AB)*(B11-AB)*aR)
D11	D11	=SUMPRODUCT(--(B11>A2:A9),(B11-A2:A9),C2:C9)
E11	E11	=SUM((B11>A2:A9)*(B11-A2:A9)*C2:C9)
F11	F11	=SUMPRODUCT(--(B11>A2:A9),(B11-A2:A9),C2:C9)

Awesome thank you. Will give these a go as well

 

[Dave Patton]

Thanks for the feedback.

With Excel 365, you can build your own self-contained formula with Lambda.

Commissions 2023.xlsm
	B	C
14	800	13,750.00
15	3000	28,750.00
16	10	200.00
1f

Cell Formulas
Range	Formula
C14:C16	C14	=Pricex(B14)

Use name manager see Formulas Name Manager and named the function something relevant.
I do not know the name that you would use; my example uses Pricex.

New name Pricex
Value =LAMBDA(Units,LET(b,{0;250;500;750;1000;1500;2000;2500},r,{20;-2.5;-2.5;-2.5;-2.5;-2.5;-2.5;-2.5},SUM((Units>b)*(Units-b)*r)))
Use Pricex like a regular function; it will show the required parameter "Units" .
examples =Pricex(B11) or =Pricex(800)

 

[excel_caveman]

Thanks for the feedback.

With Excel 365, you can build your own self-contained formula with Lambda.

Commissions 2023.xlsm
	B	C
14	800	13,750.00
15	3000	28,750.00
16	10	200.00
1f

Cell Formulas
Range	Formula
C14:C16	C14	=Pricex(B14)

Use name manager see Formulas Name Manager and named the function something relevant.
I do not know the name that you would use; my example uses Pricex.

New name Pricex
Value =LAMBDA(Units,LET(b,{0;250;500;750;1000;1500;2000;2500},r,{20;-2.5;-2.5;-2.5;-2.5;-2.5;-2.5;-2.5},SUM((Units>b)*(Units-b)*r)))
Use Pricex like a regular function; it will show the required parameter "Units" .
examples =Pricex(B11) or =Pricex(800)

Thank you. I am not familiar with LAMBDA, but I will take some time to understand it and put it into practice. Thanks for your help. Really appreciate it.