Sum & Concatenate (or similar)
 

Basically I have two cells with the start and finished column numbers that I
then need to sum. Simplified, I have tried the formula:-
=sum(concatenate("R5C",r1c1,":","R5C",r1c2))
but cannot get it to work.

If r1c1=5 & r1c2=9 the formula gives me Sum("R5C5:R5C9") which doesn't work.
[Same results with A1 type row convention]

Question: How do I get round this problem? Or get rid of the " at the
begining & end (I have tried mid,trim,index,.....)?

This works for me: =SUM(INDIRECT(CONCATENATE($A$1,"5:","c",$B$1,"5")) )
With C and I in A1, B1, respectively
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Kev H" <Kev wrote in message
...
Basically I have two cells with the start and finished column numbers that
I
then need to sum. Simplified, I have tried the formula:-
=sum(concatenate("R5C",r1c1,":","R5C",r1c2))
but cannot get it to work.

If r1c1=5 & r1c2=9 the formula gives me Sum("R5C5:R5C9") which doesn't
work.
[Same results with A1 type row convention]

Question: How do I get round this problem? Or get rid of the " at the
begining & end (I have tried mid,trim,index,.....)?

You need the INDIRECT() function

=SUM(INDIRECT("R5C"&R1C1&":R5C"&R1C2,FALSE))

"Kev H" wrote:

Basically I have two cells with the start and finished column numbers that I
then need to sum. Simplified, I have tried the formula:-
=sum(concatenate("R5C",r1c1,":","R5C",r1c2))
but cannot get it to work.

If r1c1=5 & r1c2=9 the formula gives me Sum("R5C5:R5C9") which doesn't work.
[Same results with A1 type row convention]

Question: How do I get round this problem? Or get rid of the " at the
begining & end (I have tried mid,trim,index,.....)?