SUM INDEX INDIRECT formula

[Azzavacs]

Hello,

I'm trying to use some strings to reference arrays so that I can use one cell instead of four, to visualize it better follows the example:

	A	B	C	D	E	F	G	H	I
1	=SUM(Sheet2!A1:D1)	=SUM(Sheet2!A2:D2)	=SUM(Sheet2!A3:D3)	=SUM(Sheet2!A4:D4)		=Sheet2!A1:A4	=Sheet2!B1:B4	=Sheet2!C1:C4	=Sheet2!D1:D4
2	=SUM(Sheet2!A5:D5)	=SUM(Sheet2!A6:D6)	=SUM(Sheet2!A7:D7)	=SUM(Sheet2!A8:D8)		=Sheet2!A5:A8	=Sheet2!B5:B8	=Sheet2!C5:C8	=Sheet2!D5:D8
3	=SUM(Sheet2!A9:D9)	=SUM(Sheet2!A10:D10)	=SUM(Sheet2!A11:D11)	=SUM(Sheet2!A12:D12)		=Sheet2!A9:A12	=Sheet2!B9:B12	=Sheet2!C9:C12	=Sheet2!D9:D12
4	=SUM(Sheet2!A13:D13)	=SUM(Sheet2!A14:D14)	=SUM(Sheet2!A15:D15)	=SUM(Sheet2!A16:D16)		=Sheet2!A13:A16	=Sheet2!B13:B16	=Sheet2!C13:C16	=Sheet2!D13:D16

I have the columns F:I and need the columns A:D, but since it isn't so simple my original dataset I was considering using SUM(INDEX(INDIRECT(F1:I1),1), like bellow:

	A	B	C	D
1​	=SUM(INDEX(INDIRECT(F1:I1),1))	=SUM(INDEX(INDIRECT(F1:I1),2))	=SUM(INDEX(INDIRECT(F1:I1),3))	=SUM(INDEX(INDIRECT(F1:I1),4))
2​	=SUM(INDEX(INDIRECT(F2:I2),1))	=SUM(INDEX(INDIRECT(F2:I2),2))	=SUM(INDEX(INDIRECT(F2:I2),3))	=SUM(INDEX(INDIRECT(F2:I2),4))
3​	=SUM(INDEX(INDIRECT(F3:I3),1))	=SUM(INDEX(INDIRECT(F3:I3),2))	=SUM(INDEX(INDIRECT(F3:I3),3))	=SUM(INDEX(INDIRECT(F3:I3),4))
4​	=SUM(INDEX(INDIRECT(F4:I4),1))	=SUM(INDEX(INDIRECT(F4:I4),2))	=SUM(INDEX(INDIRECT(F4:I4),3))	=SUM(INDEX(INDIRECT(F4:I4),4))

But it doesn't work, does anyone know how to correct my formula or a way to reference an array and sum only one of it's values?

In Sheet2 consider a bunch of 1, since it is an example of sums.

Any help is much appreciated!

Thank in advance!!

 

[StephenCrump]

Welcome to the Forum!

I'm not clear what the end result here is? But you're making it unnecessarily complicated, and potentially error-prone, using strings and INDIRECT functions.

If the aim is to generate the totals in A1:D4, you could use:

A1: =SUBTOTAL(9,OFFSET(Sheet2!A1:D1,SEQUENCE(4,4,0),))