How To Get The Total Of The Last Number In Every Nth Column?

[Dhira]

How To Add The last number of every nth column?
In this case every 28th column?

The first half of the formula is:

=INDEX(CP:CP,MATCH(100^100,CP:CP))

This gives the last number (in column CP)

How do I change or add to it to get the last number of every 28th column to the right to be added to it for a total?

 

[Logit]

You just now indicate you are seeking a solution for Google Sheets. Of course when you post in an Excel Forum you will receive Excel answers.

My apologies I am not able to assist. Unfamiliar with Google Sheets.

You also now indicate the columns are 5 Cols apart from one another. That does not match the "every 28 columns".

 

[Peter_SSs]

Ideally I'd love a shorter formula

As I said before, I don't think that is possible with your Excel version and the fact that you said without vba.

couldn't quite get it to work for the last one

You won't get that to work. As I said in that post ..

If you had Excel with Microsoft 365 then a formula could do this for you for as many columns as you want.

Just to demonstrate, since I have MS 365, here is that 365 formula adding the last number from every 5th column for 150 columns. I have also adjusted it to only start looking in the column from row 3 down. It also does not matter if column have no numbers yet in that area (eg column numbered 140) so it can be set up from the start to be big enough to cater for any amount of data you are likely to have.
BYW, see that you can hide columns (or rows) before using XL2BB to make the mini sheet smaller.

Dhira.xlsm
	C	D	E					J					O					T					Y					AD																																																																																																													EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX
1
2			5					10					15					20					25					30																																																																																																													139	140	141	142	143	144	145	146	147	148	149	150	151	152	153	154
3			2					1					45					5					55					45																																																																																																																			1					1
4			3					2					9					4					4					2																																																																																																																			2000					2
5			6										11					7					2					3																																																																																																																								3
6			9										17					89					6					6																																																																																																																								1000
7													21					7					6
8													33										76
9													55										122
10	50												2										2
11													5										7
12	50																						8
13																							3
14	3050																						45
15																							53
16																							2
17																							21
18
Sheet1

Cell Formulas
Range	Formula
C10	C10	=INDEX(E:E,MATCH(100^100,E:E))+INDEX(J:J,MATCH(100^100,J:J))+INDEX(O:O,MATCH(100^100,O:O))+INDEX(T:T,MATCH(100^100,T:T))+INDEX(Y:Y,MATCH(100^100,Y:Y))+INDEX(AD:AD,MATCH(100^100,AD:AD))
C12	C12	=LOOKUP(100^100,E:E)+LOOKUP(100^100,J:J)+LOOKUP(100^100,O:O)+LOOKUP(100^100,T:T)+LOOKUP(100^100,Y:Y)+LOOKUP(100^100,AD:AD)
C14	C14	=REDUCE(0,E1:EX1,LAMBDA(a,b,a+IF(MOD(COLUMN(b)-COLUMN(E1),5)=0,IFNA(LOOKUP(100^100,OFFSET(b,2,0,1000)),0),0)))

 

[Peter_SSs]

You just now indicate you are seeking a solution for Google Sheets.

Perhaps you missed post 3?

Of course when you post in an Excel Forum you will receive Excel answers.

Again, post 3 implies Excel and Google sheets so asking in Excel Questions is fine to get the Excel answer part.

You also now indicate the columns are 5 Cols apart from one another. That does not match the "every 28 columns".

No, the 5 columns is to make the sample smaller to demonstrate the concept without making a huge sheet for XL2BB and the actual data is still 28 ..

(5th in the above, but 28 in the real sheet)

 

[Peter_SSs]

If you could suggest one that works in Google Sheets alone, I'd take that,

I'm not very familiar with Google Sheets, but see if this works. This is for the 5 column examples. If that works, change the red 5 to 28 (& adjust the blue range as required)

=SUM(BYCOL(E3:EX1000,LAMBDA(col,IF(MOD(COLUMN(col)-COLUMN(E1),5)=0,IFNA(LOOKUP(100^100,col),0),0))))

 

[Dhira]

That works perfectly! Thank you.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.

This is slightly shorter and should be a more efficient Google Sheets formula though as it only processes the relevant columns. The previous formula checked every column to see if it was a "5th column" or not. You might need to experiment with or calculate the relevant entries for the red parts of the formula.

=SUM(BYCOL(CHOOSECOLS(E3:EX1000,SEQUENCE(1,30,1,5)),LAMBDA(col,IFNA(LOOKUP(100^100,col),0))))