I am trying to solve a sumproduct, with a dynamic array which starts back at position 1 for each new entry

[BlueIndian]

New business	2000	3000	4000	5000	6000
Cost	5	6	7	8	9
Remaining Balance	100%	90%	80.0%	70.0%	60.0%
Calc row1	10,000	9,000	8,000	7,000	6,000
Calc row2	-	18,000	16,200	14,400	12,600
Calc row3	-	-	28,000	25,200	22,400
Calc row4	-	-	-	40,000	36,000
Calc row5	-	-	-	-	54,000
Total	10000	27000	52200	86600	131000

The table extends across 50 columns or so, is there a way in which the Total can be calculated using a single formula

 

[Cubist]

Is this what you mean?

Book2
	A	B	C	D	E	F
1	New business	2000	3000	4000	5000	6000
2	Cost	5	6	7	8	9
3	Remaining Balance	1	0.9	0.8	0.7	0.6
4	Calc row1	10000	9000	8000	7000	6000
5	Calc row2	0	18000	16200	14400	12600
6	Calc row3	0	0	28000	25200	22400
7	Calc row4	0	0	0	40000	36000
8	Calc row5	0	0	0	0	54000
9	Total	10000	27000	52200	86600	131000
Sheet1

Cell Formulas
Range	Formula
B4:F4	B4	=$B$1*B$3*$B$2
C5:F5	C5	=$C$1*B$3*$C$2
D6:F6	D6	=$D$1*B$3*$D$2
E7:F7	E7	=$E$1*B$3*$E$2
F8	F8	=$F$1*B$3*$F$2
B9:F9	B9	=BYCOL(B4:F8,LAMBDA(x,SUM(x)))
Dynamic array formulas.

 

[BlueIndian]

thanks, i will give that a go