Calculating total units where rates vary

[VBremner]

I am trying to find a formula that will calculate the total value of a number of units, where there are different band rates that all need to be applied.

For example, I need to find the total of 385002 units.
50000 of the units are charged at £1 per unit, The next 49999 are charged at £0.9 per unit and so on. That would leave 10002 units to be charged in the lowest band.
Everything in black below is what I need on the spreadsheet, the red is just me showing the example of how it needs to calculate. I would like the outcome in cell G under total.

I have tried playing with varied iterations of "IF" and "Sumproduct" but I can't get anything to work when it gets over 1000001 units.

Any advice is appreciated, thanks

 

[Georgiboy]

I have not worked out the solution but, should the result not be closer to £285,001?

It looks like you have priced the 10002 as 124999.

 

[VBremner]

Yes thank you! That was me rushing to cobble an example together

 

[Georgiboy]

The number of units does not seem to add up, see how i have worked it out below with two helper columns:

Book1
	A	B	C	D	E	F	G
1	Quantity	Rate per unit				Number of units	Total
2	50000	£1.00	50000	£50,000.00		385002	£285,001.00
3	100000	£0.90	50000	£45,000.00
4	150000	£0.80	50000	£40,000.00
5	250000	£0.70	100000	£70,000.00
6	375000	£0.60	125000	£75,000.00
7	500000	£0.50	10002	£5,001.00
8	1000000	£0.40	0	£0.00
9	2000000	£0.30	0	£0.00
10			385002	£285,001.00
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=C2:C9*B2:B9
G2	G2	=SUM(D2:D9)
C2:C9	C2	=LET(n,IF(A2<$F$2,A2-IF(ISTEXT(A1),0,A1),$F$2-IF(ISTEXT(A1),0,A1)),IF(n<0,0,n))
C10:D10	C10	=SUM(C2:C9)
Dynamic array formulas.

 

[VBremner]

The number of units does not seem to add up, see how i have worked it out below with two helper columns:

Book1
	A	B	C	D	E	F	G
1	Quantity	Rate per unit				Number of units	Total
2	50000	£1.00	50000	£50,000.00		385002	£285,001.00
3	100000	£0.90	50000	£45,000.00
4	150000	£0.80	50000	£40,000.00
5	250000	£0.70	100000	£70,000.00
6	375000	£0.60	125000	£75,000.00
7	500000	£0.50	10002	£5,001.00
8	1000000	£0.40	0	£0.00
9	2000000	£0.30	0	£0.00
10			385002	£285,001.00
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=C2:C9*B2:B9
G2	G2	=SUM(D2:D9)
C2:C9	C2	=LET(n,IF(A2<$F$2,A2-IF(ISTEXT(A1),0,A1),$F$2-IF(ISTEXT(A1),0,A1)),IF(n<0,0,n))
C10:D10	C10	=SUM(C2:C9)
Dynamic array formulas.

Thank you!!!

We had the number of units calculated from 50001 - 100000, so not quite the 50000. I can edit this on our main spreadsheet, but I will definitely give this a go.