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 [email protected]> wrote in message
news:[email protected]...
> 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,.....)?