# Excel Formula - referencing a variable cell

#### cdhoughton

##### Board Regular
Hi,

I am trying to implement the following formula.

I have a range, say C1:N1. I can find the value of a particular cell using an INDEX(MATCH....) formula fairly easily.

However I now need to perform the formula SUM(C1: x) where x is a variable cell determined by the user.

Any ideas on how to replace x with some code to pick up this, so the formula reads e.g. SUM(C1:D1), SUM(C1:H1). Note C1 is fixed.

Any help much appreciated!!

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

##### MrExcel MVP
You didn't specify what kind of beast x is (position/number, cell address, etc.). Also, where is it entered by the user?

#### jcvoth

##### Board Regular
I tested this and it worked for me:

Sub sumcol()
endcol = InputBox("choose an ending column letter.") & "1"
Range("c2").Formula = "=SUM(c1:" & endcol & ")"
End Sub

The user chooses an ending letter (e.g. "G") and the code will sum everything from C1:G1.

Hope this helps!
-Jarrod

#### cdhoughton

##### Board Regular
Think I'm sorted now, cheers for the help!! Tried referencing a range between two different sheets !!!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,831
Messages
5,855,889
Members
431,772
Latest member
dannyboi1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back