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]

See if this works :

=SUMPRODUCT((MOD(COLUMN(A1:FS1)-COLUMN(A1),28)=0)*A1:FS1)

 

[Dhira]

Any option to do it without a vba script?
I'll need to also duplicate it on Google Sheets.

 

[Tom.Jones]

What Logit offer, is formula, not a VBA scode!

 

[Peter_SSs]

What Logit offer, is formula, not a VBA scode!

But it is also not a formula to get the last number from each of the columns as requested.

 

[Logit]

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

 

[Peter_SSs]

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

Yes, your formula adds from every 28th column, but it adds the first number (row 1 number) from each of those columns , not the last number.
(It also returns an error if any of the other columns between the 28 contain a text entry)

 

[Logit]

Try this and see if it works : =SUM(IF(MOD(COLUMN(A1:ZZ1)-COLUMN(A1),28)=0,LOOKUP(2,1/(A1:A1000<>""),A1:A1000),0))

 

[Peter_SSs]

@Dhira
I would be happy to be proved wrong but given the lack of any feasible responses, I don't think what you are asking is possible with formulas with your version of Excel unless you just do every column individually like this

INDEX(CP:CP,MATCH(100^100,CP:CP))+INDEX(DR:DR,MATCH(100^100,DR:DR))+INDEX(ET:ET,MATCH(100^100,ET:ET))

although each of those column lookups can be shortened

=LOOKUP(100^100,CP:CP)+LOOKUP(100^100,DR:DR)+LOOKUP(100^100,ET:ET)

The formula would also need modification if it is possible that one or more of the columns does not contain any numbers at all.

=IFNA(LOOKUP(100^100,CP:CP),0)+IFNA(LOOKUP(100^100,DR:DR),0)+IFNA(LOOKUP(100^100,ET:ET),0)

How many columns would you be looking at having to do this for in your workbook?

BTW, If you had Excel with Microsoft 365 then a formula could do this for you for as many columns as you want. Here is an example.

=REDUCE(0,CP1:NI1,LAMBDA(a,b,a+IF(MOD(COLUMN(b)-COLUMN(CP1),28)=0,IFNA(LOOKUP(100^100,OFFSET(b,0,0,1000)),0),0)))

 

[Dhira]

@Dhira
I would be happy to be proved wrong but given the lack of any feasible responses, I don't think what you are asking is possible with formulas with your version of Excel unless you just do every column individually like this

INDEX(CP:CP,MATCH(100^100,CP:CP))+INDEX(DR:DR,MATCH(100^100,DR:DR))+INDEX(ET:ET,MATCH(100^100,ET:ET))

although each of those column lookups can be shortened

=LOOKUP(100^100,CP:CP)+LOOKUP(100^100,DR:DR)+LOOKUP(100^100,ET:ET)

The formula would also need modification if it is possible that one or more of the columns does not contain any numbers at all.

=IFNA(LOOKUP(100^100,CP:CP),0)+IFNA(LOOKUP(100^100,DR:DR),0)+IFNA(LOOKUP(100^100,ET:ET),0)

How many columns would you be looking at having to do this for in your workbook?

BTW, If you had Excel with Microsoft 365 then a formula could do this for you for as many columns as you want. Here is an example.

=REDUCE(0,CP1:NI1,LAMBDA(a,b,a+IF(MOD(COLUMN(b)-COLUMN(CP1),28)=0,IFNA(LOOKUP(100^100,OFFSET(b,0,0,1000)),0),0)))

Appreciate it. What I'm trying to do is this (figured I'd use the BB tool)
In this example, the rows are 5 columns apart. And there are only 6 columns being calculated.

Column Lookup.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
1					Column					Column					Column					Column					Column					Column
2					5					10					15					20					25					30
3					$ 2.00					$ 1.00					$ 45.00					$ 5.00					$ 55.00					$ 45.00
4					$ 3.00					$ 2.00					$ 9.00					$ 4.00					$ 4.00					$ 2.00
5					$ 6.00										$ 11.00					$ 7.00					$ 2.00					$ 3.00
6					$ 9.00										$ 17.00					$ 89.00					$ 6.00					$ 6.00
7															$ 21.00					$ 7.00					$ 6.00
8															$ 33.00										$ 76.00
9															$ 55.00										$ 122.00
10		TOTAL OF LAST # IN COLUMNS	$ 50.00												$ 2.00										$ 2.00
11															$ 5.00										$ 7.00
12			$ 50.00																						$ 8.00
13																									$ 3.00
14			#NAME?																						$ 45.00
15																									$ 53.00
16																									$ 2.00
17																									$ 21.00
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:NI1,LAMBDA(a,b,a+IF(MOD(COLUMN(b)-COLUMN(E1),28)=0,IFNA(LOOKUP(100^100,OFFSET(b,0,0,1000)),0),0)))

The issue I have is:

• There will be more columns added over time
• With more columns, the formula gets larger and longer
• None of the columns will contain no numbers - new data will always be copied & pasted to the right
• Ideally I'd love a shorter formula that takes every Nth (5th in the above, but 28 in the real sheet) column, and adds the last numbers in the column for a total in C10

I tried your formulas and they do work but couldn't quite get it to work for the last one (see columns C). Remember I am trying to duplicate it in Google Sheets also.
The last one only captures the first column in Google Sheets (& "#NAME?" error in my Excel version).
If you could suggest one that works in Google Sheets alone, I'd take that, and use the first 2 for the Excel version. Thanks.