How to use dynamic range in Excel with COUNTIF and INDIRECT

Use the INDIRECT function in Excel formulas to change the range of cell references used in a formula without having to change the formula itself. This ensures that the same cells are used even if your spreadsheet changes.

The instructions in this article apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Mac, and Excel Online.

  1. Use dynamic range with COUNTIF – INDIRECT formula
  2. Dynamically change formula range
  3. Counting text with COUNTIF
  4. NUMBER, NUMBER BLANK and indirect
  5. Why use an indirect function?

Use dynamic range with COUNTIF – INDIRECT formula

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

Using INDIRECT as the COUNTIF argument creates a dynamic range of cell references that the function can count if the cell values ​​match the criteria. This is done by converting text data, also known as a text string, into a cell reference.

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

 = СЧЕТЕСЛИ (ДВССЫЛ (Е1 & ":" & Е2), "> 10") 

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

  • Links to cells E1 and E2 containing text data D1 and D6.

  • The range operator is a colon () in double quotes (), which turns the colon into a string.

  • Two ampersands (&) used to join or join colons with cell references E1 and E2.

As a result, INDIRECT converts the text string D1:D6 to a cell reference and passes it to the COUNTIF function to count if the referenced cells are greater than 10.

The INDIRECT function accepts any text input. These can be cells on a worksheet that contain text or text cell references that are entered directly into the function.

Dynamically change formula range

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

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

The COUNTIF function in this example only counts cells that contain numbers if they are greater than 10. Although four of the five cells in the range D1:D6 contain data, only three cells contain numbers. Cells that are empty or contain text data are ignored by the function.

Counting text with COUNTIF

The COUNTIF function is not limited to counting numeric data. It also counts cells that contain text and checks if they match certain text.

To do this, enter the following formula in cell G2:

 = СЧЕТЕСЛИ (ДВССЫЛ (Е1 & ":" & Е2), "два") 

In this formula, the INDIRECT function refers to cells B1 through B6. The COUNTIF function counts the number of cells with a text value two

In this case, the result is 1.


The other two Excel counts are COUNT, which counts cells containing any type of data, ignoring only blank or empty cells, and COUNTBLANK, which counts only empty or empty cells in a range.

Since both functions have a syntax similar to the COUNTIF function, they can be replaced with INDIRECT in the above example to create the following formulas:

 = COUNTA (INDIRECT (E1 & ":" & E2)) 

For range D1:D6, COUNTA returns 4 because four of the five cells contain data. COUNTBLANK returns 1 because there is only one blank cell in the range.

Why use an indirect function?

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

The range changes dynamically within the different functions and the results are updated accordingly.

Without the INDIRECT function, each function would have to be edited to include all 7 cells, including the new ones.

The advantages of the INDIRECT function are that text values ​​can be inserted as cell references and the ranges update dynamically as your spreadsheet changes.

This greatly simplifies general spreadsheet maintenance, especially for very large spreadsheets.

Leave a Reply

Your email address will not be published.