Tiered rate calculation with some flat rate tiers included

I was hoping someone could help me with the following, I'm trying to combine a variable rate calculation with a flat tier rate calculation. I'm having a lot of trouble.

Quantity Ordered	Rate	Is flat or variable
1-5	1000	flat
6-14	additional 200	flat
15-22	additional 200	flat
23-30	additional 100 per unit above 22	variable
31-40	additional 75 per unit above 30	variable
41-50	additional 50 per unit above 40	variable

The idea is that if you ordered 48, you would get
$1400 (the highest flat rate bracket 22 quantity units) + 8*100 (the next variable bracket for the 8 quantity ordered) + 10*75 (the next variable rate for the next 10 quantity ordered) + 8*50 (the last 8 quantity ordered at that variable bracket)


A total of $3350. Yet I can't figure this out. I'm trying to use a combination of sumproduct formulas. Anyone have any guidance?

The rate goes UP with increasing volume, then goes down?

Not exactly. I think the table could be more clear. Really the first 3 rows, up to 22 ordered quantity, are the total price. This means someone ordered 5 units would pay 1000 while someone ordering 22 would pay 1400. The rate still continues to drop, but the earlier rates can be ignored.

Is it more clear if I say

Ordered	rate	Flat or variable
1-5	1000	flat
6-14	additional 200	flat

So what is the total price for qty 1, 2, 3, ..., 7?

Row\Col	A	B	C	D	E
1	Qty	Flat	Var	Delta
2	0	$1,000	$0	$0	D2: =C2-N(C1)
3	6	$1,200	$0	$0
4	15	$1,400	$0	$0
5	22	$0	$100	$100
6	30	$0	$75	($25)
7	40	$0	$50	($25)
8
9	Qty	Total
10	1	$1,000			B10: = VLOOKUP(A10, $A$2:$B$4, 2) + SUMPRODUCT((A10 > $A$2:$A$7) * (A10 - $A$2:$A$7) * $D$2:$D$7)
11	5	$1,000
12	6	$1,200
13	14	$1,200
14	15	$1,400
15	22	$1,400
16	23	$1,500
17	24	$1,600
18	30	$2,200
19	31	$2,275
20	40	$2,950
21	41	$3,000

You're welcome.