How to Use a Dynamic Range in Excel With COUNTIF and INDIRECT

This article explains how to use the INDIRECT function in Excel formulas to change the range of cell references used in a formula without having to edit the formula itself. This ensures that the same cells are used, even when your spreadsheet changes. Information applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Mac, and Excel Online.

Use a Dynamic Range With the COUNTIF - INDIRECT Formula

The INDIRECT function can be used with a number of functions that accept a cell reference as an argument, such as the SUM and COUNTIF functions.

Using INDIRECT as the argument for COUNTIF creates a dynamic range of cell references that can be counted by the function if the cell values meet a criteria. It does this by turning text data, sometimes referred to as a text string, into a cell reference.

This example is based on the data shown in the image above. The COUNTIF - INDIRECT formula created in the tutorial is:

=COUNTIF(INDIRECT(E1&":"&E2),">10")

In this formula, the argument for the INDIRECT function contains:

• The cell references E1 and E2, which contain the text data D1 and D6.
• The range operator, the colon (:) surrounded by double quotation marks (" ") that 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:D6 into a cell reference and passes it along to the COUNTIF function to be counted if the referenced cells are larger than 10.

Dynamically Change the Formula's Range

Remember, the goal is to create a formula with a dynamic range. A dynamic range can be changed without editing the formula itself.

By changing the text data located in cells E1 and E2, from D1 and D6 to D3 and D7, the range totaled by the function can be easily changed from D1:D6 to D3:D7. This eliminates the need to directly edit the formula in cell G1.

The COUNTIF function in this example only counts cells containing numbers if they're larger than 10. Even though four of the five cells in the range of D1:D6 contain data, only three cells contain numbers. Cells that are blank or contain text data are ignored by the function.

Counting Text With COUNTIF

The COUNTIF function isn't limited to counting numeric data. It also counts cells that contain text by checking if they match a certain text.

To do this, the following formula is entered in cell G2:

=COUNTIF(INDIRECT(E1&":"&E2),"two")

In this formula, the INDIRECT function references cells B1 to B6. The COUNTIF function totals the number of cells that have the text value two in them.

In this case, the result is 1.

COUNTA, COUNTBLANK, and INDIRECT

Two other Excel count functions are COUNTA, which counts cells containing any type of data while 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 COUNTIF 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:D6, COUNTA returns an answer of 4, since four of the five cells contain data. COUNTBLANK returns an answer of 1 since there is only one blank cell in the range.

Why Use an INDIRECT Function?

The benefit of using the INDIRECT function in all of these formulas is that new cells can be inserted anywhere in the range.

The range dynamically shifts inside of the various functions, and the results update accordingly.

Without the INDIRECT function, every function would need to be edited to include all 7 cells, including the new one.

The benefits of the INDIRECT function are that text values can be inserted as cell references and that it dynamically updates ranges whenever your spreadsheet changes.

This makes overall spreadsheet maintenance much easier, especially for very large spreadsheets.