I'm trying to sum over a range the end of which is defined by some calculation. The problem boils down to using the address function to define the range. For example to sum C2 to C43:


But this is not recognised as a range. Why not and what should I use?

1 Answer 1


Create a dynamic range and use:


see this example.

  • Thanks, @Daniele. I can also put the top of the range (C10 in your example) in (say) cell F2, and use C2:indirect(F2). But I also want to sum column D and I seem to have to do the whole thing again. I thought I could just put "10" in a cell and use it twice. I couldn't make that work. (Maybe I'm just trying to be too economical with intermediate calculations.)
    – Pansmanser
    Commented Mar 17, 2022 at 20:58
  • @Pansmanser go to see the modified sheet...now sums two columns with only one "until" cell
    – Daniele
    Commented Mar 17, 2022 at 21:17
  • Easy when you know how! Thanks, Daniele!
    – Pansmanser
    Commented Mar 17, 2022 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.