Range based on cell value in Excel

Calculating ranges is easy. But have you ever tried to calculate a range based on the number in a specific cell?

In this lesson, I’ll show you a formula where you make such a reference.

Calculate range based on cell value

  1. Enter numbers from 1 to 10 into the first column.
  2. In cell C1 enter the phrase – Number.
  3. In cell D1 enter the phrase – Result.
  4. Enter the number between 1 and 1048576 into cell C2.
  5. Insert the following code into cell B2.
=SUM(A1:INDIRECT(CONCATENATE("A",C2)))

This is our result.

I told you to enter the value between 1 and 1048576 because these are the first and the last row in Excel. If you enter a different value, text, or leave a blank cell, it will return the #REF! error.

You can handle this exception by modifying the formula.

=IF(AND(C2>=1,C2<=1048576),SUM($A$1:INDIRECT(CONCATENATE("A",C2))),"bad number")

Now, if you enter the number outside this range the exception will be handled.