Summarize numbers from every third column

[Gabriell]

Hi Guys,

I have a huge excel table with 100+ columns and I'd like to summarize only the values from every third column.
There is a solution for this? I summarized one by one but almost every day new columns are added and I have to extend the formula after each change.

Thank you in advance!
Gabor

 

[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’)

Maybe

=SUMPRODUCT((MOD(COLUMN(B2:DA2)-1,3)=0)*(B2:DA2))

 

[jasonb75]

This will pick up new columns as they are added, needs office 365 / excel 2021.

Book1
	A	B	C	D	E	F	G	H	I	J
2	15	2	5	4	7	5	2	1	6	9
3	36	12	11	7	22	33	18	2	2	11
Sheet3

Cell Formulas
Range	Formula
A2:A3	A2	=SUM(INDEX(2:2,,SEQUENCE(,MATCH(1E+100,2:2),4,3)))

 

[Gabriell]

This will pick up new columns as they are added, needs office 365 / excel 2021.

Book1
	A	B	C	D	E	F	G	H	I	J
2	15	2	5	4	7	5	2	1	6	9
3	36	12	11	7	22	33	18	2	2	11
Sheet3

Cell Formulas
Range	Formula
A2:A3	A2	=SUM(INDEX(2:2,,SEQUENCE(,MATCH(1E+100,2:2),4,3)))

It works, I use Office 365.
Thank you for the quick suport!

 

[Gabriell]

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’)

Maybe

=SUMPRODUCT((MOD(COLUMN(B2:DA2)-1,3)=0)*(B2:DA2))

I use Office 365.
This solution works as well. Thank you!

 

[Fluff]

I use Office 365.

Thanks for that, If you update your Account details (or click your user name at the top right of the forum) 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’) Saves us from having to ask everytime

 

[Gabriell]

This will pick up new columns as they are added, needs office 365 / excel 2021.

Book1
	A	B	C	D	E	F	G	H	I	J
2	15	2	5	4	7	5	2	1	6	9
3	36	12	11	7	22	33	18	2	2	11
Sheet3

Cell Formulas
Range	Formula
A2:A3	A2	=SUM(INDEX(2:2,,SEQUENCE(,MATCH(1E+100,2:2),4,3)))

I received an error at the 3rd row. What could be the problem?

 

[jasonb75]

You haven't edited it correctly from the example, although it would appear that you understand how it works. I've made some changes based on the screen capture that you've provided. Enter this one into F5 then fill down.

=E5-SUM(INDEX(5:5,,SEQUENCE(,(MATCH(1E+100,5:5)-8)/4,12,4)))

 

[Gabriell]

You haven't edited it correctly from the example, although it would appear that you understand how it works. I've made some changes based on the screen capture that you've provided. Enter this one into F5 then fill down.

=E5-SUM(INDEX(5:5,,SEQUENCE(,(MATCH(1E+100,5:5)-8)/4,12,4)))

Yes, you've right. I've just noticed before you replied me. It works excellent!
Thank you again!