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

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

Using INDIRECT as an argument to COUNTIF creates a dynamic range of cell references that the function can count if the cell values meet a criterion. This is done by converting text data, sometimes called 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: