Dynamically sum up columns in a table

[LearnNewThings]

Hello!

I am looking to sum up all columns to the right of column J (sum will reside in column J). The data is in a table that could have additional columns added at a later time, so I want to be able to build the formula with dynamic abilities to adjust as new columns are added.

Thanks in advance!

 

[dalvin647]

Hi LearnNewThings,

This should work as long as your table starts on column A. And is actually a table in excel.

Formula below:
=SUM(OFFSET([@J],,1):OFFSET([@J],,COLUMNS(Table)-COLUMN([@J])))

Change as req'd. The J in the formula, change to column name. 'Table' -> rename to what your table is actually called.

Logic as follows:
-OFFSET([@J],,1) - Sum starting from the cell to the right of Column J

-OFFSET([@J],,COLUMNS(Table)-COLUMN([@J])) - Finds the last column in the table by counting however many columns there are in the table then subtracting what the number for Column J = 10

Good luck!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	A	B	C	D	E	F	G	H	I	J	1	2	3	4	5	6	7	8	9	10	11
2										11	1	1	1	1	1	1	1	1	1	1	1
3										22	2	2	2	2	2	2	2	2	2	2	2
4										33	3	3	3	3	3	3	3	3	3	3	3
5										44	4	4	4	4	4	4	4	4	4	4	4
6										55	5	5	5	5	5	5	5	5	5	5	5
7										66	6	6	6	6	6	6	6	6	6	6	6
Sheet1

Cell Formulas
Range	Formula
J2:J7	J2	=SUM(OFFSET([@J],,1):OFFSET([@J],,COLUMNS(Table)-COLUMN([@J])))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 a non volatile option is

=SUM(DROP(Table1[@],,10))

 

[LearnNewThings]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 a non volatile option is

=SUM(DROP(Table1[@],,10))

I've updated my account to show I am using 365.

This solution was very clean and worked out perfectly! Thanks so much!!

 

[Fluff]

I've updated my account to show I am using 365.

Thanks for that.

Glad we could help & thanks for the feedback.