Sumproduct question for tiered pricing grid

E

ExcelHelpPls

New Member
Joined
Jun 20, 2011
Messages
10
Greetings,

Background:

I have a grid of unit ranges, and prices for different client types.

Column A is the minimum of the range
Column B is the Maximum of the range (currently not used in my formulas).
Column C is the price for Client type 1
Column D is the price for Client type 2
Column E is the price for Client type 3

When calculating the total price of an order, the tiers are graduated, so that the units 0-1000 cost .35 each and 1001-5000 are .33 each. So an order of 2000 should equal $680 (.35*1000+.33*1000). The following formula works for the calulation (where P9 is the number of units):

=SUMPRODUCT(--(P9>{0,1000,5000}),(P9-{0,1000,5000}), {0.35,-0.02,-0.03})

My question: Is there a way to replace the "0,1000,5000" and ".35,-.02,-.03" array items with cell references, so that I can easily change the prices of an item without having to also change the individual formula? I tried logically to simply replace them with A1, A2, A3 and C1, C2, C3, and the formula doesn't recognize the cells.

Any help is greatly appreciated!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
<TABLE style="WIDTH: 401pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=535><COLGROUP><COL style="WIDTH: 257pt; mso-width-source: userset; mso-width-alt: 12544" width=343><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 height=20 width=343 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 width=64 align=right>0.35</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 height=20 align=right>1000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 align=right>-0.02</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 height=20 align=right>5000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 align=right>-0.03</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow; WIDTH: 257pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl583 height=20 width=343 align=right>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 401pt; HEIGHT: 105pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl584 height=140 rowSpan=7 width=535 colSpan=4>Array formula confirm CTRL+SHIFT+ENTER: =SUMPRODUCT(--(P9>(CHOOSE(ROW(INDIRECT("1:"&COUNT(A1,A2,A3))),A1,A2,A3))),(P9-(CHOOSE(ROW(INDIRECT("1:"&COUNT(A1,A2,A3))),A1,A2,A3))),(CHOOSE(ROW(INDIRECT("1:"&COUNT(B1,B2,B3))),B1,B2,B3)))


Where P9 =40
</TD></TR><TR style="HEIGHT: 15pt" height=20></TR><TR style="HEIGHT: 15pt" height=20></TR><TR style="HEIGHT: 15pt" height=20></TR><TR style="HEIGHT: 15pt" height=20></TR><TR style="HEIGHT: 15pt" height=20></TR><TR style="HEIGHT: 15pt" height=20></TR></TBODY></TABLE>
 
Upvote 0
Thanks for the feedback, but it doesn't seem to be working correctly for me.

I think it might have to do with the prices you have listed. The actual prices are .35, .33, and .30. I used the -.02, and -.03 in my formula to get it to calculate correctly.

If I use your formula for 1010 units (P9) it returns 356.80, but should return 353.30.

If I use your formula for 6000 units (P9) it returns 4050, but should return 1970.

Definitely seems to be close though, and if you have the time I'd be very interested in a walkthrough of the formula you provided.

Thanks again
 
Upvote 0
<TABLE style="WIDTH: 401pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=535><COLGROUP><COL style="WIDTH: 257pt; mso-width-source: userset; mso-width-alt: 12544" width=343><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 height=20 width=343 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 width=64 align=right>0.35</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 width=64 align=right>1000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 width=64 align=right>2000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 height=20 align=right>1000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 align=right>0.33</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 height=20 align=right>5000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582 align=right>0.3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow; WIDTH: 257pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl583 height=20 width=343 align=right>680</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl582> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 401pt; HEIGHT: 105pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl584 height=140 rowSpan=7 width=535 colSpan=4>With a helper column C.Input D1.Array formula confirm CTRL+SHIFT+ENTER: =SUMPRODUCT(--(D1>(CHOOSE(ROW(INDIRECT("1:"&COUNT(A1,A2,A3))),A1,A2,A3))),(D1-(CHOOSE(ROW(INDIRECT("1:"&COUNT(A1,A2,A3))),A1+C1,A2*C2,A3))),(CHOOSE(ROW(INDIRECT("1:"&COUNT(B1,B2,B3))),B1,B2,B3)))</TD></TR><TR style="HEIGHT: 15pt" height=20></TR><TR style="HEIGHT: 15pt" height=20></TR><TR style="HEIGHT: 15pt" height=20></TR><TR style="HEIGHT: 15pt" height=20></TR><TR style="HEIGHT: 15pt" height=20></TR><TR style="HEIGHT: 15pt" height=20></TR></TBODY></TABLE>
 
Upvote 0
try this,
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Quantity</td><td style=";">Price</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.35</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style=";">Quantities</td><td style="text-align: right;;">2,000</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1001</td><td style="text-align: right;;">0.33</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style=";">TotalPrice</td><td style="text-align: right;;">680.02</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">5001</td><td style="text-align: right;;">0.30</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">E3>=A3:A5</font>),(<font color="Red">E3-A3:A5</font>),(<font color="Red">B3:B5-B2:B4</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
MrVillareal said:
try this,
Excel 2010
ABCDE
1QuantityPrice
2
300.35Quantities2,000
410010.33TotalPrice680.02
550010.30

<tbody>
</tbody>
Sheet3​

Worksheet Formulas
CellFormula
E4=SUMPRODUCT(--(E3>=A3:A5),(E3-A3:A5),(B3:B5-B2:B4))

<tbody>
</tbody>

<tbody>
</tbody>
Click to expand...

Hi I've been trying to get this formula into my logistic costs sheet but still found it went error.

My data is simpler :

0-3 container: 9800000
4-10 container: 9000000

Can you please help me solve the problem? Thanks in advance.
 
Last edited:
Upvote 0
ExcelHelpPls said:
Thanks for the help!

Mr. Villareal's solution is what I was looking for.
Click to expand...

try..

Excel 2010
ABCDE
2
309800000Quantities5
439000000TOtalPrice47400000
5

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
E4=SUMPRODUCT(--(E3>=A3:A4),(E3-A3:A4),(B3:B4-B2:B3))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Make sure you have space just like those highlighted in yellow.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
populate Total amount from different cells across workbooks based on sheets names
Replies
0
Views
228
KalilMe
K
K
  • Question
populate amounts for column debit,credit for each customer from file to another
Replies
0
Views
210
KalilMe
K
K
  • Question
extraction invoices information from multiple sheets based on DATE TODAY
Replies
1
Views
253
Fluff
Fluff
K
  • Question
compare data across sheets for 6000 rows for each sheet individual based on sheet name
Replies
0
Views
196
KalilMe
K
K
  • Solved
extraction invoices detail for TOTAL row for each sheet individual
Replies
7
Views
256
RudRud
R

Forum statistics

Threads
1,215,135
Messages
6,123,241
Members
449,093
Latest member
Vincent Khandagale

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top