Using Variables in Summing Formulas in Excel/Open Calc

[Kuryakin]

In excel, using Open Calc but I assume they're the same.

Is their a way to put variables value into summing formulas. I wanted to sum from A1 to an A cell which is numbered A1+F4. So if F4 = 100 I'd want to SUM(A1:A101)
I tried SUM(A1:A(1+F4)) but it gives a number error

 

[JaredJames]

Hi, ok you need to use a function called "INDIRECT()".

Example: =SUM(INDIRECT("A1:A"&F4))

Note: & is the concatenation operator, not +.

 

[Kuryakin]

Thank-you.

 

[JhonMoney]

SUM(A1:A(1+F4)) this formula i don't think is correct.
simply use with + sign

 

[LudusRex]

Yes, there is a way to use variables in summing formulas in Excel and Open Calc. You can use the INDIRECT function to reference a cell address that is based on the value of a variable. In this case, you can use the formula =SUM(A1:INDIRECT("A"&(1+F4))) to achieve the desired result. This formula will first concatenate the letter "A" with the value of F4 (100 in this case) to create a cell reference of A101, and then use the INDIRECT function to convert this text into a cell reference that can be used in the SUM function. This will sum the cells from A1 to A101 as desired.