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

- Enter numbers from 1 to 10 into the first column.
- In cell
**C1**enter the phrase –**Number**. - In cell
**D1**enter the phrase –**Result**. - Enter the number between
**1**and**1048576**into cell**C2**. - Insert the following code into cell
**B2**.

1 |
=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.

1 |
=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.