Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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,.....)? |
#2
|
|||
|
|||
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,.....)? |
#3
|
|||
|
|||
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,.....)? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF, IF, Concatenate? | Excel Worksheet Functions | |||
Using Concatenate inside a vlookup | Excel Worksheet Functions | |||
Concatenate in Pocket Excel | Excel Worksheet Functions | |||
Concatenate cells in Pocket Excel | Excel Discussion (Misc queries) | |||
Concatenate cells without specifying/writing cell address individually | Excel Discussion (Misc queries) |