Help with SUMPRODUCT for tiered pricing table

emoody

New Member
Joined
Apr 20, 2022
Messages
2
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?

MinMaxPrice
049990.685
500099990.645
10000249990.615
25000499990.59
50000999990.57
1000002499990.555
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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?
 
Upvote 0
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)
 
Upvote 0
I haven't turned this into a single formula, but is this at least calculating what you expect?

Tiered fee calculation.xlsx
ABCDE
1Tier MinimumTier MaximumRatePrice
2049990.685$3,424.32
3500099990.645$3,225.00
410000249990.615$615.62
525000499990.59$0.00
650000999990.57$0.00
71000002499990.555$0.00
8
9Amount11000
10Result$7,264.93
Sheet2
Cell Formulas
RangeFormula
E2E2=MAX(0,MIN(C2,$B$9)*D2)
E3:E7E3=MAX(0,MIN(C3-C2,$B$9-C2)*D3)
B10B10=SUM(E2:E7)
 
Upvote 0
This seems to work using SUMPRODUCT in one cell.

Book1
ABCDEFG
1Tier MinimumTier MaximumRatePriceRate Difference
2049990.6853424.3150.685
3500099990.645645.645-0.04
410000249990.6150-0.03
525000499990.590-0.025
650000999990.570-0.02
71000002499990.5550-0.015
8
9Amount6000
10Result4069.96
11
12With SUMPRODUCT4069.96
Sheet1
Cell Formulas
RangeFormula
E2E2=MAX(0,MIN(C2,$B$9)*D2)
E3:E7E3=MAX(0,MIN(C3-C2,$B$9-C2)*D3)
G3:G7G3=D3-D2
B10B10=SUM(E2:E7)
B12B12=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})
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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