COUNT - INDIRECT Formula Overview
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.
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
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.