1

I have a SUM formula =SUM(F1:F10), how can I make the "10" dynamic by referencing to a cell value? For example, cell D2 has the value 10 so my goal is to concatenate "F" with the value of cell D2. If I change the cell value of D2 from 10 to 20 then the formula should update.

I am thinking of something like: =SUM(F7:INDIRECT("F" & TEXT($D$2,"0")))

But I need the INDIRECT("F") dynamic as it formula needs to be applied to column G as well.

Sorry if this question is elementary but I already forgot how it works.

2 Answers 2

0

An alternative is to use the =OFFSET() function to define your summand.

For example OFFSET(F1,0,add_col,rows_to_sum,1) defines a range 1 column wide which starts at add_col columns to the right of cell F1 extends row_to_sum rows downwards, so

OFFSET(F1,0,0,10,1) is equivalent to F1:F10
OFFSET(F1,0,0,20,1) is equivalent to F1:F20
OFFSET(F1,0,1,10,1) is equivalent to G1:G10
OFFSET(F1,0,1,20,1) is equivalent to G1:G20

add_col and rows_to_sum can be suitable cell references e.g. A1, D2 etc.

Simply wrap the OFFSET()within a =SUM() to get something like =SUM(OFFSET(F1,0,A1,D2,1)) using relative/absolute cell references ($ signs) as appropriate.

If you don't always wish to start summation from the first worksheet row then use a value other than 0 for the second argument of the OFFSET() function. For example:

OFFSET(F1,6,0,14,1) is equivalent to F7:F20

and again a cell reference can be used for this second argument.

Similarly, if you want to make the summation cover multiple columns then use a value other than 1 for the fifth argument of the OFFSET() function. For example:

OFFSET(F1,6,0,14,2) is equivalent to F7:G20

and again a cell reference can be used for this fifth argument.

See https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66 for details about worksheet function =OFFSET()

0

SUBSTITUTE(ADDRESS(1,F7,4),"1","") will return the column letter for cell F7. This will help get the column, you might be able to incorporate this with your formula by using this:

=SUM(F1:INDIRECT(SUBSTITUTE(ADDRESS(1,F7,4),"1","") & TEXT($D$2,"0")))

2
  • Do you really mean F7 in ADDRESS(1,F7,4)? For this expression not to return a #VALUE! error cell F7will need to contain a valid worksheet column number - so 6 for worksheet column F, 7 for G, etc. But F7 is the start cell of the summand range, so it will contain whatever needs to be summed. An alternative would be to use a different cell, say D1 in the ADDRESS() snippet but, in this case, why not more simply dispense with SUBSTITUTE(ADDRESS)) and put the column letter in D1 and use =SUM(F7:INDIRECT(D1&TEXT($D$2,"0"))) instead?
    – DMM
    Commented Feb 2, 2022 at 12:08
  • Thanks @DMM , the first argument was incorrect, I've updated the answer. Your OFFSET solution is quite elegant - thanks for teaching me something new!
    – Daniel
    Commented Feb 2, 2022 at 15:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .