help with SUMPRODUCT to calculate tiered discount

[milsnips]

ive got a pretty simple scenario,

lets say cost of product X = $100
buy up to 2 = %10 off = $90
buy 3-5 = 15% off = $85
buy 6-9 =20% off = $80
buy 10 or more = 25% off = $75

if someone buys 7 products, the formula should calculate:


(2*90) + (3*85)+(2*80) = $595.

Can someone help me put this into a SUMPRODUCT formula? thx

 

[pike]

Hello,
Where A1 = the number of items and B1 the Cost of item then
in C1 ==(A1*B1)-(SUMPRODUCT((A1>{0,2,5,9})*(A1-{0,2,5,9})*{0.1,0.05,0.05,0.05}))*B1

 

[pike]

Hello,
Where A1 = the number of items and B1 the Cost of item then
in C1 =(A1*B1)-(SUMPRODUCT((A1>{0,2,5,9})*(A1-{0,2,5,9})*{0.1,0.05,0.05,0.05}))*B1

 

[NotoriousPopol]

Hi! No need for SUMPRODUCT. Here, I've put the price of the product in A1, then in A3:A6 (1,3,6,10) then in B3:B6 (2,5,9,10000000) and in C3:C6 (10,15,20,25). The number of product orderded is in A10.

Here you go : =SUM(IF($A$10>=A3:A6,IF($A$10>=B3:B6,(B3:B6-A3:A6+1)*($A$1-$A$1*C3:C6/100),($A$10-A3:A6+1)*($A$1-$A$1*C3:C6/100)),0)).

YOU NEED TO PRESS CTRL+SHIFT+ENTER as this is an array formula.

 

[Marcelo Branco]

Maybe...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Low​

[/TD]
[TD]

High​

[/TD]
[TD]

%​

[/TD]
[TD]

Discount​

[/TD]
[TD][/TD]
[TD]

Price​

[/TD]
[TD]

Qty​

[/TD]
[TD]

Total​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

10%​

[/TD]
[TD]

10%​

[/TD]
[TD][/TD]
[TD]

100​

[/TD]
[TD]

7​

[/TD]
[TD]

595​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

5​

[/TD]
[TD]

15%​

[/TD]
[TD]

105%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

6​

[/TD]
[TD]

9​

[/TD]
[TD]

20%​

[/TD]
[TD]

105%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD]

25%​

[/TD]
[TD]

105%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


in D2
=C2

In D3 copied down till D5
=1+(C3-C2)

Price in F2
Quantity in G2

Formula in H2
=SUMPRODUCT(--(G2>=A2:A5),G2-A2:A5+1,(1-D2:D5)*F2)

Hope this helps

M.