Sum across variable columns

[coop123]

Hi

I think this may already have been answered but I cannot find a solution.

I have a macro in which i want to sum up the values across multiple columns. The number of columns will vary each time report is run.

See example spreadsheet.

In the example in D4 I want to sum all values in K4 to CU4, then copy formula down to D27 summing up each row separately.

End range for row 4 can vary but will always start at K4
End range for formula in column D will vary but will always start at D4.

Thanks

Regards

coop123

mr excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU
1	Components										9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	9.48E+08	24222420CH	24222420D	24222420R01	24223020D	24223020KW	24223020R01	24223020RU	24223410CH	24223410R01	24223420CH	24223420D	24223420GB	24223420R01	24224020GB	24224020R01	24224120USA	24601250CH	24601250D	24601250GB	24601250R01	24601250RU	24714040D	24714040R01	24724040D	24724040GB	24724040KW	24724040R01	24724040RU	24724040USA	24724410R01	24724410RU	24724420CH	24724420R01	24724430R01	24724440CH	24724440D	24724440GB	24724440R01	EBA2748	WOSP30100SS	EVMDD5306SS	ICBSPO30TE/S	MDD30CM/B/TH	MDD30TE/S/TH
2				month data is for							61	558	1987	821	48	296	469	1897	371	262	72	32	241	50	127	20	23	100	151	30	53	11	114	894	40	280	12	200	40	40	74	480	100	40	12	60	411	72	147	232	75	78	38	530	14	20	30	20	379	20	343	4	25	50	75	240	14	150	19	261	70	10	100	113	94	10	50	40	100	5	20	120	4	248	30	1	20	203	48	30	40	21	60	495	3	4	168	352	352
3				Total Qty
4	FPADBA020URK5			197							0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	100	1	94	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	FPADBA021URK4										0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	100	1	94	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	FPADBA075URK1										0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2	704
7	FPADBA085URK0										0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	168	0	0
8	FPADBA086URK0										0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	168	0	0
9	FPADBA089URK0										0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	50	1	100	1	20	1	4	1	30	1	20	1	48	1	40	1	60	0	0	0	0	0	0
10	FPADBA092URK1										0	558	0	0	0	1	469	0	0	0	72	1	241	0	127	1	23	0	151	0	0	0	114	0	0	0	0	0	0	0	0	0	0	0	0	1	411	0	0	0	75	1	0	0	0	1	30	1	379	1	343	1	25	1	75	1	14	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11	FPADBA093URK0										0	558	0	0	0	1	469	0	0	0	72	1	241	0	127	1	23	0	151	0	0	0	114	0	0	0	0	0	0	0	0	0	0	0	0	1	411	0	0	0	75	1	0	0	0	1	30	1	379	1	343	1	25	1	75	1	14	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12	FPADBA094URK0										0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	50	1	100	1	20	1	4	1	30	1	20	1	48	1	40	1	60	0	0	0	0	0	0
13	FPADBA097URK0										0	0	1987	1	0	0	0	1	371	1	0	0	0	0	0	0	0	1	0	0	0	0	0	1	40	0	0	0	40	0	0	1	100	0	0	0	0	0	0	1	0	0	0	1	14	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14	FPADBA098URK0										0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
15	FPADBA099URK0										0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
16	FPADBA101URK0										0	0	1987	0	0	0	0	0	371	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	40	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
17	FPADBA102URK0										0	0	1987	0	0	0	0	0	371	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	40	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
18	FPADBA104URK0										0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
19	FPADBA105URK0										0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
20	FPADBA106URK0										0	0	0	1	0	0	0	1	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	40	0	0	1	100	0	0	0	0	0	0	0	0	0	0	0	14	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
21	FPADBA107URK0										0	0	0	1	0	0	0	1	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	40	0	0	1	100	0	0	0	0	0	0	0	0	0	0	0	14	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
22	FPADBA108URK0										0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
23	FPADBA113URK0										0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	3	1	0	0	0
24	FPADBA114URK0										0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	3	1	0	0	0
25	FPADBA115URK0										61	0	0	0	48	0	0	0	0	0	0	0	0	1	0	0	0	0	0	1	53	1	0	0	0	1	12	1	0	0	74	0	0	1	12	0	0	1	147	0	0	0	38	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	19	1	70	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
26	FPADBA116URK0										61	0	0	0	48	0	0	0	0	0	0	0	0	1	0	0	0	0	0	1	53	1	0	0	0	1	12	1	0	0	74	0	0	1	12	0	0	1	147	0	0	0	38	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	19	1	70	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
27	FPADBA117URK0										61	0	0	0	48	0	0	0	0	0	0	0	0	1	0	0	0	0	0	1	53	1	0	0	0	1	12	1	0	0	74	0	0	1	12	0	0	1	147	0	0	0	38	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	19	1	70	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
D4	D4	=SUM(K4:CU4)

 

[Peter_SSs]

the number of columns will vary each time report is run.

How would we know how many columns for a particular report run?
If there is nothing further to the right, couldn't you just always add from column K across to column, say, ZZ?

 

[coop123]

Hi Peter_SSs

Thanks for the pointer. I was over thinking the process. I can extend the formula to cover the furthest column.

Keeping it simple is best solution.

Thanks

coop123

 

[Peter_SSs]

You're welcome. Just need a second pair of eyes sometimes.