Dynamic Formula to Sum Cells in Row

[Gos-C]

Hi all,

I have the following formula:

{="=SUM(C"&ROW(OFFSET(APR!$J$1,MAX(ROW(APR!$J:$J)*(APR!$J:$J<>""))-12,0))&":N"&ROW(OFFSET(APR!$J$1,MAX(ROW(APR!$J:$J)*(APR!$J:$J<>""))-12,0))&")"}

It returns:

=SUM(C408:N408)

But I need it to return the value (i.e, the sum of cells C408 to N408).

Any how?

Thank you,
Gos-C

 

[Steve=True]

Use the =Indirect() function

 

[Gos-C]

I used:

{=SUM(INDIRECT("C"&ROW(OFFSET(APR!$J$1,MAX(ROW(APR!$J:$J)*(APR!$J:$J<>""))-12,0))&":N"&ROW(OFFSET(APR!$J$1,MAX(ROW(APR!$J:$J)*(APR!$J:$J<>""))-12,0))&")"))}

But it give the #REF! error.

What have I done wrong?

Gos-C

 

[Steve=True]

I got a ref# error when i uploaded your formula, so i thought it was working on your side. If you wrap indirect around your formula, it should have worked i thought anyway.

Maybe there is a better way let me look at your formula

 

[Steve=True]

what are you trying to lookup ?

 

[Gos-C]

Hi Steve,

I have several similarly formatted reports, which varies in length, and I want to sum a range in the 12th from last row of each report.

Gos-C

 

[Steve=True]

Try this:

Hopefully some of the quotes didn't leave the reply

=SUM(INDIRECT(("C"&IF(ISERROR(MATCH(9.999999E+306,J:J)),MATCH("*",J:J,-1),
IF(ISERROR(MATCH("*",J:J,-1)),MATCH(9.999999E+306,J:J),
MAX(MATCH(9.999999E+306,J:J),MATCH("*",J:J,-1))))-12)&":n"&IF(ISERROR(MATCH(9.999999E+306,J:J)),MATCH("*",J:J,-1),
IF(ISERROR(MATCH("*",J:J,-1)),MATCH(9.999999E+306,J:J),
MAX(MATCH(9.999999E+306,J:J),MATCH("*",J:J,-1))))-12))

Last row formula from: http://www.grbps.com/Excel7.pdf

by the way, how do you insert a box to post your formula?

 

[Steve=True]

looks like the post worked.

note you do NOT need to enter this as an array. just a normal formula.

 

[Gos-C]

Hi Steve,

It didn't work; I got 0 (zero). BTW, my data is on a different sheet (named APR) in the same workbook.

The cells have the following values:

C	D	E	F	G	H	I	J	K	L	M	N
0	66	71	63	135	0	6	0	22	0	35	2

 

[Steve=True]

Yes you would get zero if the sheet you put it in was not APR

copy the previous formula and put it in any part of sheet APR but not in column J Then cut and paste it to where you want it.

Alternately, i did a find and replace for you How nice am i :

=SUM(INDIRECT(("C"&IF(ISERROR(MATCH(9.999999E+306,APR!J:APR!J)),MATCH("*",APR!J:APR!J,-1),
IF(ISERROR(MATCH("*",APR!J:APR!J,-1)),MATCH(9.999999E+306,APR!J:APR!J),
MAX(MATCH(9.999999E+306,APR!J:APR!J),MATCH("*",APR!J:APR!J,-1))))-12)&":n"&IF(ISERROR(MATCH(9.999999E+306,APR!J:APR!J)),MATCH("*",APR!J:APR!J,-1),
IF(ISERROR(MATCH("*",APR!J:APR!J,-1)),MATCH(9.999999E+306,APR!J:APR!J),
MAX(MATCH(9.999999E+306,APR!J:APR!J),MATCH("*",APR!J:APR!J,-1))))-12))