How to Use a Dynamic Range in Excel With COUNTIF and INDIRECT GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office

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

By Ted French Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. lifewire's editorial guidelines Updated on April 26, 2022 Tweet Share Email Tweet Share Email MS Office Excel Word Powerpoint Outlook

What to Know

The INDIRECT function changes the range of cell references in a formula without editing the formula.Use INDIRECT as the argument for COUNTIF to create a dynamic range of cells that meet specified criteria.The criteria are established by the INDIRECT function, and only cells that meet the criteria are counted. 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. Screenshot 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. The INDIRECT function accepts any text inputs. These can be cells in the worksheet that contain text or text cell references that are entered directly into the function.

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. Screenshot 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. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Use the COUNTIF Function in Excel How to Round Numbers Down in Excel With the ROUNDDOWN Function How to Count Data in Selected Cells With Excel's COUNTIF Function How to Use the Round Function in Excel How to Use the Google Spreadsheets AVERAGE Function Use the Excel RIGHT Function to Extract Characters Excel SUMIFS: Sum Only Values Meeting Multiple Criteria How to Count Blank or Empty Cells in Google Sheets Use Excel's EOMONTH Function to Add or Subtract Months How to Use the Excel INDEX Function How to Use the Excel DATE Function Ignore Error Values When Finding the Average in Excel How to Use the COUNTIFS Function in Excel How to Use the Excel TRUNC Function Count Cells of Data With Excel's SUMPRODUCT Function How to Use AVERAGEIF Function in Excel Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies