Sumproduct multi-tiered thresholds

[KingCeli]

I'm trying to find out if there is a formula to calculate the monthly billing fee based on the below table.

I receive monthly reports from a customer. The below table shows the billing rate per product at each threshold level.
Below the table is an example of how it is supposed to be calculated.
Starting with 9000 units of Product A and 1000 units from Product B bill at their respective first threshold rates, and then so on with Product C, D,..H.
I'm familiar with using Sumproducts to discount from the total at different levels, but that would only work on one product. I don't know how I would go about writing a formula for these many products.

I would very much appreciate help with this problem, I would waste too much time at work figuring it out on my own and this is the only customer that has their payment like this.

Book1
	B	C	D	E	F	G	H	I	J
4	Thresholds	Product A	Product B	Product C	Product D	Product E	Product F	Product G	Product H
5	1 to 10,000	$1.35	$1.21	$1.18	$1.09	$1.02	$0.87	$0.82	$0.71
6	10,001 to 50,000	$1.28	$1.15	$1.12	$1.03	$0.97	$0.83	$0.81	$0.67
7	50,001 to 100,000	$1.22	$1.09	$1.07	$0.98	$0.92	$0.79	$0.77	$0.64
8	100,001 to 250,000	$1.16	$1.03	$1.01	$0.93	$0.87	$0.75	$0.73	$0.61
9	250,001 to 500,000	$1.10	$0.98	$0.96	$0.89	$0.83	$0.71	$0.69	$0.58
10	500,001 to 750,000	$1.40	$0.93	$0.92	$0.84	$0.79	$0.68	$0.66	$0.55
11	750,001 to 1,000,000	$0.99	$0.89	$0.87	$0.80	$0.75	$0.64	$0.62	$0.52
12	1,000,001 to 3,000,000	$0.94	$0.84	$0.82	$0.76	$0.71	$0.61	$0.59	$0.50
13
14	Sample Fee Calculation:
15	Product A – 9000 units
16	Product B – 12000 units
17	Product C – 5000 units
18	Product D – 10000 units
19	Product E – 2000 units
20	Product F – 7000 units
21	Product G – 1000 units
22	Product H – 25000 units
23
24	Fee Calculation = (1.35×9000(A)) + (1.21×1000(B)) + (1.15×11000(B)) + (1.12×5000(C)) + (1.03×10000(D)) + (.97×2000(E)) + (.83×7000(F)) + (.81×1000(G)) + (.67×4000(H)) + (.64×21000(H)) = $66,590.00
Sheet1

 

[kweaver]

I don't think your B product calculation is correct. I tried a slightly different approach to allow the formula to be filled.
Will this work? I think I calculated correctly. Obviously, check the math.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Low Thresh	High Thresh	Product A	Product B	Product C	Product D	Product E	Product F	Product G	Product H
2	1	10000	$1.35	$1.21	$1.18	$1.09	$1.02	$0.87	$0.82	$0.71
3	10001	50000	$1.28	$1.15	$1.12	$1.03	$0.97	$0.83	$0.81	$0.67
4	50001	100000	$1.22	$1.09	$1.07	$0.98	$0.92	$0.79	$0.77	$0.64
5	100001	250000	$1.16	$1.03	$1.01	$0.93	$0.87	$0.75	$0.73	$0.61
6	250001	500000	$1.10	$0.98	$0.96	$0.89	$0.83	$0.71	$0.69	$0.58
7	500001	750000	$1.40	$0.93	$0.92	$0.84	$0.79	$0.68	$0.66	$0.55
8	770001	1000000	$0.99	$0.89	$0.87	$0.80	$0.75	$0.64	$0.62	$0.52
9	1000001	3000000	$0.94	$0.84	$0.82	$0.76	$0.71	$0.61	$0.59	$0.50
10
11			9000	12000	5000	10000	2000	7000	1000	25000
12		FEE	$12,150.00	$14,400.00	$5,900.00	$10,900.00	$2,040.00	$6,090.00	$820.00	$17,150.00
Sheet8

Cell Formulas
Range	Formula
C12:J12	C12	=MIN(C11,$B2)*C2+MAX(0,MIN($B3-$B2,C11-$B2))*C3+MAX(0,MIN($B4-$B3,C11-$B3))*C4+MAX(0,MIN($B5-$B4,C11-$B4))*C5+MAX(0,MIN($B6-$B5,C11-$B5))*C6+MAX(0,MIN($B7-$B6,C11-$B6))*C7+MAX(0,MIN($B8-$B7,C11-$B7))*C9+MAX(0,MIN($B9-$B8,C11-$B8))*C9

 

[Dave Patton]

Book1
	C	D	E	F	G	H	I	J	K	L
4	Thresholds	Product A	Product B	Product C	Product D	Product E	Product F	Product G	Product H
5
6	0	$1.35	$1.21	$1.18	$1.09	$1.02	$0.87	$0.82	$0.71
7	10,000	$1.28	$1.15	$1.12	$1.03	$0.97	$0.83	$0.81	$0.67
8	50,000	$1.22	$1.09	$1.07	$0.98	$0.92	$0.79	$0.77	$0.64
9	100,000	$1.16	$1.03	$1.01	$0.93	$0.87	$0.75	$0.73	$0.61
10	250,000	$1.10	$0.98	$0.96	$0.89	$0.83	$0.71	$0.69	$0.58
11	500,000	$1.40	$0.93	$0.92	$0.84	$0.79	$0.68	$0.66	$0.55
12	750,000	$0.99	$0.89	$0.87	$0.80	$0.75	$0.64	$0.62	$0.52
13	1,000,000	$0.94	$0.84	$0.82	$0.76	$0.71	$0.61	$0.59	$0.50
14
15
16
17	Sample Fee Calculation:	9,000	12,000	5,000	10,000	2,000	7,000	1,000	25,000
18		12,150	14,400	5,900	10,900	2,040	6,090	820	17,150	69,450
19
9b

Cell Formulas
Range	Formula
D18:K18	D18	=SUMPRODUCT(--(D17>$C$6:$C$13),D17-$C$6:$C$13,D6:D13-D5:D12)
L18	L18	=SUM(D18:K18)

 

[kweaver]

Dave: nice use of SUMPRODUCT. Didn't think of that initially when I was plowing through the calculation logic.

 

[KingCeli]

I don't think I explained it right. All the products need to be calculated cumulatively and starting with Product A. so the 9000 units will be calculated at @ $1.35 first then 1000 from Product B @$1.21 towards the first threshold. Then the remaining units of B (11000) billed at $1.15. Product C-G and 4000 of H making 50,000 units billed at the second level threshold. the last 21,000 units for H are billed at the third threshold rate $.64.

 

[Dave Patton]

I do not follow your explanation.
The example below assumes that you have previous amounts.
You have new amounts for the current period (month).
The calculation is based on the total.
The amount for the current month would be the new total - previous amount.
If this is not what you require, please post an example of your actual challenge.

Book1
	C	D	E	F	G	H	I	J	K	L
2	Brackets	Product A	Product B	Product C	Product D	Product E	Product F	Product G	Product H
3	blank row
4	0	1.35	1.21	1.18	1.09	1.02	0.87	0.82	0.71
5	10,000	1.28	1.15	1.12	1.03	0.97	0.83	0.81	0.67
6	50,000	1.22	1.09	1.07	0.98	0.92	0.79	0.77	0.64
7	100,000	1.16	1.03	1.01	0.93	0.87	0.75	0.73	0.61
8	250,000	1.10	0.98	0.96	0.89	0.83	0.71	0.69	0.58
9	500,000	1.40	0.93	0.92	0.84	0.79	0.68	0.66	0.55
10	750,000	0.99	0.89	0.87	0.80	0.75	0.64	0.62	0.52
11	1,000,000	0.94	0.84	0.82	0.76	0.71	0.61	0.59	0.50
12
13	Previous	5,000	5,000	5,000	5,000	5,000	5,000	5,000	5,000
14	Current	10,000	10,000	16,000	22,000	28,000	34,000	40,000	46,000
15	Total	15,000	15,000	21,000	27,000	33,000	39,000	45,000	51,000
16	Amount	19,900	17,850	24,120	28,410	32,510	32,770	36,550	34,540	226,650
17	Previous
18	Current
9b

Cell Formulas
Range	Formula
D15:K15	D15	=D13+D14
D16:K16	D16	=SUMPRODUCT(--(D15>$C$4:$C$11),D15-$C$4:$C$11,D4:D11-D3:D10)
L16	L16	=SUM(D16:K16)

 

[KingCeli]

Apologies, this customer's agreement is overly complicated.
The example below is how I would calculate the long way the total amount due for October.

All the reported units count toward the tiered thresholds but need to be calculated in order from A-H.

Book1
	B	C	D	E	F	G	H	I	J
4	Thresholds	Product A	Product B	Product C	Product D	Product E	Product F	Product G	Product H
5	1 to 10,000	$1.35	$1.21	$1.18	$1.09	$1.02	$0.87	$0.82	$0.71
6	10,001 to 50,000	$1.28	$1.15	$1.12	$1.03	$0.97	$0.83	$0.81	$0.67
7	50,001 to 100,000	$1.22	$1.09	$1.07	$0.98	$0.92	$0.79	$0.77	$0.64
8	100,001 to 250,000	$1.16	$1.03	$1.01	$0.93	$0.87	$0.75	$0.73	$0.61
9	250,001 to 500,000	$1.10	$0.98	$0.96	$0.89	$0.83	$0.71	$0.69	$0.58
10	500,001 to 750,000	$1.40	$0.93	$0.92	$0.84	$0.79	$0.68	$0.66	$0.55
11	750,001 to 1,000,000	$0.99	$0.89	$0.87	$0.80	$0.75	$0.64	$0.62	$0.52
12	1,000,001 to 3,000,000	$0.94	$0.84	$0.82	$0.76	$0.71	$0.61	$0.59	$0.50
13
14		October Reporting			Thresold reached
15	Product A	15000			10000	Product A	10,000	$1.35	$13,500.00
16	Product B	48000				Product A	5,000	$1.28	$6,400.00
17	Product C	52000			50000	Product B	35,000	$1.15	$40,250.00
18	Product D	60000				Product B	13,000	$1.09	$14,170.00
19	Product E	98000			100000	Product C	37,000	$1.07	$39,590.00
20	Product F	150000				Product C	15,000	$1.01	$15,150.00
21	Product G	142000				Product D	60,000	$0.93	$55,800.00
22	Product H	950000			250000	Product E	75,000	$0.87	$65,250.00
23		1515000				Product E	23,000	$0.83	$19,090.00
24						Product F	150,000	$0.71	$106,500.00
25					500000	Product G	77,000	$0.69	$53,130.00
26						Product G	65,000	$0.66	$42,900.00
27					750000	Product H	185,000	$0.55	$101,750.00
28					1000000	Product H	250,000	$0.52	$130,000.00
29						Product H	515,000	$0.50	$257,500.00
30									$960,980.00
Sheet2

Cell Formulas
Range	Formula
C23	C23	=SUM(C15:C22)
H15	H15	=F15
H16	H16	=C15-H15
H17	H17	=F17-H16-H15
H18	H18	=C16-H17
H19	H19	=F19-H18-H17-H16-H15
H20	H20	=C17-H19
H21	H21	=C18
H22	H22	=F22-H21-H20-H19-H18-H17-H16-H15
H23	H23	=C19-H22
H24	H24	=C20
H25	H25	=F25-H24-H23-H22-H21-H20-H19-H18-H17-H16-H15
H26	H26	=C21-H25
H27	H27	=F27-H26-H25-H24-H23-H22-H21-H20-H19-H18-H17-H16-H15
H28	H28	=F28-H27-H26-H25-H24-H23-H22-H21-H20-H19-H18-H17-H16-H15
H29	H29	=C22-H28-H27
J15:J29	J15	=H15*I15
J30	J30	=SUM(J15:J29)

 

[Dave Patton]

This post calculates just for product H.
Hopefully it answers your question.

Book1
	C	D	E	F	G	H	I	J	K
2	Brackets	Product A	Product B	Product C	Product D	Product E	Product F	Product G	Product H
3	blank row
4	0	1.35	1.21	1.18	1.09	1.02	0.87	0.82	0.71
5	10,000	1.28	1.15	1.12	1.03	0.97	0.83	0.81	0.67
6	50,000	1.22	1.09	1.07	0.98	0.92	0.79	0.77	0.64
7	100,000	1.16	1.03	1.01	0.93	0.87	0.75	0.73	0.61
8	250,000	1.10	0.98	0.96	0.89	0.83	0.71	0.69	0.58
9	500,000	1.40	0.93	0.92	0.84	0.79	0.68	0.66	0.55
10	750,000	0.99	0.89	0.87	0.80	0.75	0.64	0.62	0.52
11	1,000,000	0.94	0.84	0.82	0.76	0.71	0.61	0.59	0.50
12
13	Previous								565,000
14	October								950,000
15	Total	0	0	0	0	0	0	0	1,515,000
16	Amount	0	0	0	0	0	0	0	827,400
17	Previous								338,150
18	Current								489,250
19
9b

Cell Formulas
Range	Formula
D15:K15	D15	=D13+D14
D16:K16	D16	=SUMPRODUCT(--(D15>$C$4:$C$11),D15-$C$4:$C$11,D4:D11-D3:D10)
K18	K18	=K16-K17

 

[KingCeli]

Sorry, it does not, thanks for trying, I guess I'm not explaining it right.

 

[Dave Patton]

You can consider the cumulative amount with the SumProduct.
Review the previous posts and/or the following.

Book1
	C	D	E	F	G	H	I	J	K	L
2	Brackets	Product A	Product B	Product C	Product D	Product E	Product F	Product G	Product H	Total
3	blank row
4	0	1.35	1.21	1.18	1.09	1.02	0.87	0.82	0.71
5	10,000	1.28	1.15	1.12	1.03	0.97	0.83	0.81	0.67
6	50,000	1.22	1.09	1.07	0.98	0.92	0.79	0.77	0.64
7	100,000	1.16	1.03	1.01	0.93	0.87	0.75	0.73	0.61
8	250,000	1.10	0.98	0.96	0.89	0.83	0.71	0.69	0.58
9	500,000	1.40	0.93	0.92	0.84	0.79	0.68	0.66	0.55
10	750,000	0.99	0.89	0.87	0.80	0.75	0.64	0.62	0.52
11	1,000,000	0.94	0.84	0.82	0.76	0.71	0.61	0.59	0.50
12
13	October	15,000	48,000	52,000	60,000	98,000	150,000	142,000	950,000
14	Cumulative	15,000	63,000	115,000	175,000	273,000	423,000	565,000	1,515,000
15			15,000	63,000	115,000	175,000	273,000	423,000	565,000
16	Amount	19,900	72,270	125,250	170,850	244,590	316,730	404,000	827,400	2,180,990
17	Less	0	17,850	70,510	115,050	160,250	210,230	307,970	338,150	1,220,010
18	Oct Amount	19,900	54,420	54,740	55,800	84,340	106,500	96,030	489,250	960,980
9b

Cell Formulas
Range	Formula
E14:K14	E14	=SUM($D$13:E13)
E15:K15	E15	=E14-E13
D16:K16	D16	=SUMPRODUCT(--(D14>$C$4:$C$11),D14-$C$4:$C$11,D4:D11-D3:D10)
L16:L18	L16	=SUM(D16:K16)
D17:K17	D17	=SUMPRODUCT(--(D15>$C$4:$C$11),D15-$C$4:$C$11,D4:D11-D3:D10)
D18:K18	D18	=D16-D17