INDIRECT Formula used to Total a Dynamic Range of Values
This example is based on the data shown in the image above.
The SUM - INDIRECT formula created by using the tutorial steps below is:
=SUM(INDIRECT("D" &E1& ":D" &E2))
In this formula, the nested INDIRECT function's argument contains references to cells E1 and E2. The numbers in those cells, 1 and 4, when combined with the rest of INDIRECT's argument, form the cell references D1 and D4.
As a result, the range of numbers totaled by the SUM function is the data contained in the range of cells D1 to D4 - which is 50.
By changing the numbers located in cells E1 and E2; however, the range to be totaled can be easily changed.
This example will first use the above formula to total the data in cells D1:D4 and then change the summed range to D3:D6 without editing the formula in cell F1.
This example is based on the data shown in the image above.
The SUM - INDIRECT formula created by using the tutorial steps below is:
=SUM(INDIRECT("D" &E1& ":D" &E2))
In this formula, the nested INDIRECT function's argument contains references to cells E1 and E2. The numbers in those cells, 1 and 4, when combined with the rest of INDIRECT's argument, form the cell references D1 and D4.
As a result, the range of numbers totaled by the SUM function is the data contained in the range of cells D1 to D4 - which is 50.
By changing the numbers located in cells E1 and E2; however, the range to be totaled can be easily changed.
This example will first use the above formula to total the data in cells D1:D4 and then change the summed range to D3:D6 without editing the formula in cell F1.
No comments:
Post a Comment