COUNTIFS with Variable Range: Dynamic Criteria in Excel

COUNTIFS with a variable range lets you count cells that meet multiple criteria, where the range itself changes based on a cell value, a named range, or an Excel Table. You enter the criteria range as a reference that expands or shifts, and you get a count that updates automatically. This is essential for dashboards, dynamic reports, and any spreadsheet where new data is added regularly.

Formula

The general formula for a variable range in COUNTIFS is:

=COUNTIFS(INDIRECT("A"&start_row&":A"&end_row), ">"&criteria)

Where:

• start_row – the first row of the range (a number, e.g., 2)
• end_row – the last row of the range (a number, e.g., 11)
• criteria – the value or text you are matching (e.g., 100)
• INDIRECT – converts the text string into a real range reference

The example above counts cells in column A from row r to row r+n-1 that are greater than c. If you need multiple criteria, add more range/criteria pairs: COUNTIFS(range1, crit1, range2, crit2).

Worked Example

Using the default values from the calculator: start row = 2, number of rows = 10, criteria = 100. The range is A2:A11. Suppose the values in A2:A11 are: 150, 80, 200, 95, 120, 60, 180, 110, 90, 130.

Count of values greater than 100: 150, 200, 120, 180, 110, 130 = 6.

The formula =COUNTIFS(INDIRECT("A"&2&":A"&11),">"&100) returns 6.

When to Use a Variable Range

Use a variable range when your data range is not fixed – for example, when you add rows daily, or when you want to let a user pick a date range or category. Static ranges like A2:A100 break when you insert rows. A variable range built with OFFSET, INDEX, or INDIRECT adjusts automatically.

Common scenarios:

• Counting sales in a specific month, where the month number is in a cell
• Counting entries in the last N days, where N is user-controlled
• Counting values in a dynamic named range that grows with new data

How to Build a Variable Range

There are three main ways to create a variable range:

1. INDIRECT – Build the range as text. Example: COUNTIFS(INDIRECT("A"&B1&":A"&B2), ">10"). This is the most flexible but volatile (recalculates every time).
2. OFFSET – Start from a base cell and offset by rows/cols. Example: COUNTIFS(OFFSET(A1,0,0,B1,1), ">10") where B1 is the height. Also volatile.
3. INDEX – Returns a reference to a cell, often used to define the last row. Example: COUNTIFS(A2:INDEX(A:A,B1), ">10"). Non-volatile and more efficient.

For a simple dynamic range that grows with data, an Excel Table is the best choice: COUNTIFS(Table1[Column1], ">10") – the range automatically expands when you add rows.

Common Mistakes and How to Avoid Them

• Using the wrong syntax for criteria – When using a comparison operator, you must concatenate: ">"&B1 not ">B1".
• Mixing data types – If your criteria is text, put it in quotes: "Apple". If it’s a cell reference, don’t quote the cell: B1.
• Volatile functions slowing down the sheet – INDIRECT and OFFSET recalculate on every change. Use INDEX or a Table if your sheet is large.
• Off-by-one errors – When using start row and count, the end row is start+count-1, not start+count.
• Whole-column references – A:A works but is inefficient. Limit the range to the actual data area.

Alternatives to COUNTIFS with Variable Range

If you find COUNTIFS too limiting, consider:

• SUMPRODUCT – Can handle arrays and multiple conditions without needing a range reference. Example: =SUMPRODUCT((A2:A100>10)*(B2:B100="Yes")).
• FILTER function (Excel 365) – Returns an array of matching rows, then you can use COUNTA on it. Example: =COUNTA(FILTER(A2:A100, A2:A100>10)).
• PivotTables – For interactive filtering and counting without formulas.
• Power Query – For complex data transformation and counting before loading into Excel.

Performance and Volatility

Volatile functions (INDIRECT, OFFSET) recalculate whenever any change is made in the workbook, which can slow down large files. If you have thousands of formulas, prefer non-volatile options:

• Use INDEX to define the last cell of a range.
• Use Excel Tables – they are non-volatile and automatically expand.
• Limit the range size – e.g., A2:A10000 instead of A:A.

Also, note that COUNTIFS is not case-sensitive. If you need case-sensitive counting, use SUMPRODUCT with EXACT.

Frequently Asked Questions

How do I make COUNTIFS use a cell value as the range?

Use INDIRECT with the cell value as the range text. For example, if B1 contains “A2:A100”, then =COUNTIFS(INDIRECT(B1), ">10") counts values greater than 10 in that range.

Why does my COUNTIFS return #VALUE! when using INDIRECT?

This usually happens when the text string in INDIRECT is not a valid range. Check that the row numbers are positive integers and that the column letter is correct. Also, if the sheet name contains spaces, you must wrap it in single quotes: "'My Sheet'!A2:A100".

Can I use a named range in COUNTIFS?

Yes, you can use a named range directly: =COUNTIFS(MyRange, ">10"). If you want the named range to be dynamic, define it with OFFSET or a Table, and it will adjust automatically.

How do I count with multiple criteria and a variable range?

Just add more range/criteria pairs. For example: =COUNTIFS(INDIRECT("A"&start&":A"&end), ">"&c1, INDIRECT("B"&start&":B"&end), "<"&c2). Ensure all ranges have the same number of rows.

Is there a way to avoid INDIRECT because it's volatile?

Yes, use INDEX to define the range. For example, to count values in column A from row 2 to the last row with data in column B: =COUNTIFS(A2:INDEX(A:A,COUNTA(B:B)), ">10"). This is non-volatile and more efficient.

Can COUNTIFS handle a range that changes size when I add rows?

Yes, if you use an Excel Table. Convert your data to a Table (Ctrl+T), then use structured references: =COUNTIFS(Table1[Values], ">10"). The range automatically expands when you add rows to the table.

Related

• Unhide Chat in Viber: Step-by-Step Guide
• Retries, Timeouts, and Circuit Breakers: When to Use Each
• Clipboard Access Denied: Error Explained

in r | Start row | - | 2 | 1 | 100000
in n | Number of rows | - | 10 | 1 | 100000
in c | Criteria value | - | 100 | 0 | 1000000
out count | Count of values > criteria | - | COUNTIFS(INDIRECT("A"&r&":A"&(r+n-1)),">"&c) | 0