Autosum every n cell

[rkramapo]

Hi,

I'm trying to figure out how to auto sum every n cell- so in the screenshot that I have, I need to auto sum GN4, GR4, GV4, and so on... and then I need to auto sum GO4, GS4, GW4, and so on.

Also GP14, GT14, GX14, and so on.. and then GQ14, GU14, GY14, and so on.

Thanks.

 

[Vogateer]

You have 365 listed in your Office version, so I'm hoping that's what you're using here.

Seems like this will do the trick for the GN,GR,GV group:

=SUM(FILTER(GN4:GY4,MOD(COLUMN(GN4:GY4),4)=0))

The 4 is for every 4th cell, and you adjust the 0 at the end of the formula to match the remainder of the column number divided by 4. For example, for the GO,GS,GW group the remainder is 1:

=SUM(FILTER(GN4:GY4,MOD(COLUMN(GN4:GY4),4)=1))

 

[FormR]

Hi, another option might be to make use of your headers with a SUMIFS() formula, for example:

=SUMIFS($GN4:$GY4,$GN$3:$GY$3,"Sales")

and

=SUMIFS($GN4:$GY4,$GN$3:$GY$3,"Count")

 

[Fluff]

Another option

=SUM(CHOOSECOLS(GN4:GY4,SEQUENCE(,3,1,4)))

for GO etc change the 1 to a 2

 

[rkramapo]

You have 365 listed in your Office version, so I'm hoping that's what you're using here.

Seems like this will do the trick for the GN,GR,GV group:

=SUM(FILTER(GN4:GY4,MOD(COLUMN(GN4:GY4),4)=0))

The 4 is for every 4th cell, and you adjust the 0 at the end of the formula to match the remainder of the column number divided by 4. For example, for the GO,GS,GW group the remainder is 1:

=SUM(FILTER(GN4:GY4,MOD(COLUMN(GN4:GY4),4)=1))

Apologies, I should have stated for 2016.

 

[Sanjeev1976]

You can try :

=SUM(INDEX(GN4:$GY4,,1), INDEX(GN4:$GY4,,5), INDEX(GN4:$GY4,,9))

 

[Vogateer]

You can use FormR's idea to make it more readable, and I believe this will work on 2016, but I'm not 100%:

=SUM((GN14:GY14)*(GN$3:GY$3="Sales"))

 

[FormR]

I'm pretty sure that SUMIFS() was around in Excel 2016

 

[Vogateer]

I'm pretty sure that SUMIFS() was around in Excel 2016

I may have a bad source or may have read it incorrectly, but I got the impression that SUMIFS was a 2019 feature.

rkramapo: please try FormR's formula before trying mine.

 

[Fluff]

SUMIFS came out with 2007.