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,.....)?