Using the INDIRECT function in Excel formulas makes it easy to change the range of cell references used in the formula without having to edit the formula itself.
INDIRECT can be used with a number of functions that accept a cell reference as an argument such as the SUM and COUNT functions.
In the latter case, using INDIRECT as the argument for COUNT creates a dynamic range of cell references that can be totaled by the function.
INDIRECT does this by turning text data  sometimes referred to as a text string  into a cell reference.
Example: Using a Dynamic Range with the COUNT  INDIRECT Formula
This example is based on the data shown in the image above.
The COUNT  INDIRECT formula created in the tutorial is:
=COUNT( INDIRECT( E1 & ":" & E2))
In this formula, the argument for the INDIRECT function contains:
 the cell references E1 and E2  which contain the text data D1 and D5
 the range operator  the colon (:) surrounded by double quotation marks (" "), which turns the colon into a text string
 Two ampersands (&) that are used to concatenate or join together the colon with the cell references E1 and E2
The result is that INDIRECT converts the text string D1 : D5 into a cell reference and passes it along to COUNT function to be totaled.
Dynamically Changing the Formula's Range
Remember, the goal is to create a formula with a dynamic range  one that can be changed without editing the formula itself.
By changing the text data located in cells E1 and E2, from D1 and D5 to D3 and D6 for example, the range totaled by the function can be easily changed from D1:D5 to D3:D6.
This eliminates the need to edit the formula in cell G1.
Entering the Tutorial Data
 Enter the following data into cells D1 to E2

Cell Data D1  1 D2  two D3  3 D5  5 D6  six E1  D1 E2  D5 F1  Count:
Entering the COUNT  INDIRECT Formula
 Click on cell G1  this is where the results of this example will be displayed
 Enter the formula: =COUNT(INDIRECT(E1&":"&E2))
 Press the Enter key on the keyboard to complete the function
 The cell G1 should contain the answer of 3
Note the COUNT function only counts cells containing numbers, so even though four of the five cells in the range of D1 : D5 contain data, only three cells contain numbers.
Cells that are blank or contain text data are ignored by the function.
Modifying the Formula's Range
 Click on cell E1
 Enter the cell reference D3
 Press the Enter key on the keyboard to move to cell E2
 In this cell enter the cell reference D6
 Press the Enter key on the keyboard
 The answer in cell G1 should change to 2 since only two cells in the new range D3:D6 contain numbers
COUNTA, COUNTBLANK and INDIRECT
Two other Excel count functions are COUNTA  which counts cells containing any type of data  ignoring only blank or empty cells, and COUNTBLANK, which counts only blank or empty cells in a range.
Since both of these functions have similar syntax to the COUNT function, they can be substituted into the above example with INDIRECT to create the following formulas:
=COUNTA( INDIRECT( E1 & ":" & E2))
=COUNTBLANK( INDIRECT( E1 & ":" & E2))
For the range D1:D5, COUNTA would return an answer of 4  since four of the five cells contain data, and OUNTBLANK and answer of 1  since there is only one blank cell in the range.