sum number of columns based on variable value

[Guest]

I need to add the values in columns C2 to AL2 with the number of columns to
be included in the SUM depending on a variable that is entered by the user.
Cells C2 to AL2 contain the values and the row below C3 to AL3 contain the
formula to add the specified number of columns in the row above.

For example, if the user enters the value 3 in cell A1 then the formula
should add the 3 columns preceeding the cell with the formula. If the formula
is in cell G3, then the cells E2:G2 should be added. If the forumla is in
cell H3 then the cells F2:G2 should be added and so on.

I need something like this, if this formula were in cell G3: Sum(G2-A1:G2).
I know this is not the correct syntax but hopefully it shows what I am trying
to do.

Thank you in advance for your help.

 

[Guest]

Hi,
Try this in C3:

=SUM(INDIRECT(CHAR(MAX(65,65+COLUMN()-$A$1))&2&":"&CHAR(64+COLUMN())&2))

copy across to AL3

HTH
Jean-Guy

 

[Guest]

That worked perfectly. Thank You!!

Hi,
Try this in C3:

=SUM(INDIRECT(CHAR(MAX(65,65+COLUMN()-$A$1))&2&":"&CHAR(64+COLUMN())&2))

copy across to AL3

HTH
Jean-Guy

 

[Guest]

This formula works up to column Z, however, once I get into the columns AA,
AB, etc I get a #REF! error. I think the CHAR function is returning a symbol
rather than a letter from Char(91) and on.

How can I get the formula to work for columns beyond Z?

Thanks

 

[Guest]

Hi,

Sorry for being late, I did not see your last post until now!

This should work better:

=SUM(INDIRECT(ADDRESS(2,MAX(1,COLUMN()-$A$1))&":"&ADDRESS(2,COLUMN())))

Cheers!
Jean-Guy