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 the 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.
