Function Problems

How to Use COUNTIFS with INDIRECT and a Dynamic Last Row in Excel

Algirdas Jasaitis Oct 9, 2026 868 views

Question details

The user needs to create a COUNTIFS formula where the range dynamically ends at a specific row number that is stored in a separate cell.

How to Use COUNTIFS with INDIRECT and a Dynamic Last Row in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating conditional counts using dynamic range sizes without manually updating the formula whenever the dataset grows.
Observed behavior: The user needs to successfully construct a valid dynamic range reference by concatenating a static text string with a dynamic cell value inside the INDIRECT function.

Before you start

Ensure the cell containing your dynamic row number (e.g., AK74) contains a valid numerical value and no text, as an invalid row number will cause the formula to return a #REF! error.

Solution 1Recommended

Use the INDIRECT Function with String Concatenation

Combine the INDIRECT function and the ampersand (&) operator to dynamically link your static column reference with a changing row number stored in another cell.

The INDIRECT function evaluates a text string and translates it into a valid cell reference. When working with a dynamic row, you can pass the static part of the range (like "Year!K2:K") as a text string and append the dynamic cell value using the ampersand (&).

Identify the static part of your range

Determine the starting cell and the column letter for your criteria range. For example, if your data starts at K2 on the 'Year' sheet, your static text is "Year!K2:K".

Locate your dynamic row cell

Identify the cell that contains the last row number. For example, cell $AK$74.

Concatenate the string

Use the ampersand (&) to join the static text and the dynamic cell: "Year!K2:K"&$AK$74.

Wrap in the INDIRECT function

Place this concatenated string inside the INDIRECT function so Excel can read it as a range: INDIRECT("Year!K2:K"&$AK$74).

Insert into your COUNTIFS formula

Construct your full formula using the dynamic ranges for both the criteria ranges. Example: =COUNTIFS(INDIRECT("Year!K2:K"&$AK$74), V$1, INDIRECT("Year!G2:G"&$AK$74), $U2).

Use the INDIRECT Function with String Concatenation

Performance Warning: INDIRECT is a volatile function, meaning it recalculates every time any change is made in the workbook. In large datasets, this can slow down performance.

Solution 2

Alternative: Use the INDEX Function for a Non-Volatile Dynamic Range

Instead of using the volatile INDIRECT function, use INDEX to define a dynamic range end point. This method prevents workbook slowdowns.

Effortlessly Manage Dynamic Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like COUNTIFS, INDIRECT, and INDEX, making it incredibly easy to handle dynamic data analysis tasks exactly as you would in Microsoft Excel.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
2. Enter the formula: Click on your target cell and type your formula, e.g., =COUNTIFS(INDIRECT("Year!K2:K"&$AK$74),V$1).
3. Calculate and verify: Press Enter to instantly apply the dynamic calculation and view your results.

100% compatible with Microsoft Excel formulas, functions, and .xlsx formats.Seamlessly handles advanced array calculations and volatile functions like INDIRECT.Lightweight software that ensures smooth calculation performance, even with heavy data.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDIRECT formula return a #REF! error?

A #REF! error usually occurs if the concatenated string does not form a valid Excel reference. Check if the cell containing the dynamic row number is empty or contains text. Also, ensure you have included any necessary quotation marks around worksheet names that contain spaces.

Can I use INDIRECT to reference closed external workbooks?

No, the INDIRECT function only evaluates references to external workbooks if that workbook is currently open in Excel. If the referenced external workbook is closed, INDIRECT will return a #REF! error.

How do I reference a worksheet with spaces in its name using INDIRECT?

When referencing a sheet name that contains spaces, you must wrap the sheet name in single quotation marks within your text string. For example: INDIRECT("'My Sales Data'!K2:K"&$AK$74).

Is it better to use OFFSET or INDIRECT for dynamic ranges?

Both OFFSET and INDIRECT are volatile functions and can slow down your workbook. While both work for dynamic ranges, using INDEX is generally considered the best practice because it is non-volatile and calculates much faster on large datasets.

Excel & Data Problems