Excel COUNTIFS Multiple Criteria Range Explained

Formulas & functions · Updated November 22, 2026

The direct answer: use =COUNTIFS(range1, criteria1, range2, criteria2, ...) where each range must be the same size and shape, and each criteria can be a number, text, cell reference, or wildcard. For example, =COUNTIFS(A2:A100,"West",B2:B100,">100") counts rows where column A equals “West” and column B is greater than 100.

What COUNTIFS Actually Does

COUNTIFS is the multi-condition version of COUNTIF. It counts cells that meet all the conditions you specify, using AND logic by default. Each range/criteria pair is an independent test, and a row is counted only if every test passes for that row. The function was introduced in Excel 2007, so it works in all modern versions including Excel 365, Excel 2021, and Excel for the web. On Mac, the syntax and behavior are identical, but you separate arguments with commas just like on Windows.

Key rule: all ranges must have the same number of rows and columns. If you use A2:A100 and B2:B99, Excel returns a #VALUE! error. Also, ranges do not need to be adjacent—you can count from columns A and D in the same formula.

How to Write the Formula Correctly

Assume you have a sales table with region in A2:A100 and sales amount in B2:B100. To count rows where region is “West” and sales is over 100:

  • Click an empty cell, type =COUNTIFS(A2:A100,"West",B2:B100,">100") and press Enter.
  • Result: the number of rows where both conditions are true. If there are 15 West rows with sales above 100, it returns 15.

Criteria can be entered directly in quotes, or referenced from a cell. For example, if E1 contains “West”, use =COUNTIFS(A2:A100,E1,B2:B100,">100"). This makes the formula easier to update without editing the formula itself.

For text criteria, you can use wildcards: * matches any sequence of characters, ? matches a single character. So =COUNTIFS(A2:A100,"W*",B2:B100,">100") counts all regions starting with W. To count cells that literally contain an asterisk or question mark, precede it with a tilde (~).

Using Multiple Criteria on the Same Range

Sometimes you need to count cells that meet either of two conditions on the same column, like counting sales that are either “West” or “East”. COUNTIFS cannot do OR logic directly—it always applies AND across pairs. Instead, add two COUNTIFS together: =COUNTIFS(A2:A100,"West",B2:B100,">100") + COUNTIFS(A2:A100,"East",B2:B100,">100"). This counts rows that meet either the first set or the second set.

For more complex OR logic with multiple columns, you might need SUMPRODUCT or the newer FILTER function (available in Excel 2021 and Microsoft 365). For example, =ROWS(FILTER(A2:B100,((A2:A100="West")+(A2:A100="East"))*(B2:B100>100))) counts rows where region is West or East AND sales > 100. FILTER is more flexible but requires a newer version.

Common Mistakes and How to Fix Them

The most frequent error is mismatched range sizes. Always double-check that each range has the same start and end row. Another common issue is using a criteria that Excel doesn’t interpret as text. If you type =COUNTIFS(A2:A100,West) without quotes, Excel treats West as a named range or a formula, not text. Always put text criteria in double quotes, or reference a cell containing the text.

For numeric comparisons, remember to include the operator inside the quotes: ">100" not >100. If you omit the quotes, Excel tries to parse it as a formula and fails. Also, blank cells are ignored unless you specifically count them with "" as the criteria. To count non-blank cells, use "<>" (not equal to nothing).

Another pitfall: using the entire column (e.g., A:A) is allowed, but it can slow down large workbooks because Excel processes all 1,048,576 rows. Prefer a defined range like A2:A1000 unless you need the whole column.

COUNTIFS vs. SUMIFS, AVERAGEIFS, and SUMPRODUCT

COUNTIFS counts; SUMIFS adds values from a sum range when conditions are met. For example, =SUMIFS(B2:B100,A2:A100,"West") sums sales for West. AVERAGEIFS returns the average of a range that meets conditions. All three share the same logic and range-size rules.

SUMPRODUCT is an older alternative that can handle OR logic and array calculations. For instance, =SUMPRODUCT(((A2:A100="West")+(A2:A100="East"))*(B2:B100>100)) counts rows where region is West or East and sales > 100. It multiplies boolean arrays (TRUE/FALSE become 1/0) and sums the results. SUMPRODUCT works in all Excel versions but is less readable than COUNTIFS for simple AND cases.

If you have Excel 365, consider using the FILTER function with ROWS to count filtered rows, or the newer COUNTIFS with the @ operator for dynamic arrays. But for most everyday tasks, COUNTIFS is the simplest and fastest.

Practical Examples and Real Scenarios

Example 1: Count orders placed after January 1, 2024, with a quantity greater than 5. With dates in C2:C100 and quantities in D2:D100, use =COUNTIFS(C2:C100,">"&DATE(2024,1,1),D2:D100,">5"). The ampersand concatenates the comparison operator with the date value.

Example 2: Count employees in department “Sales” who have a performance rating of “A” or “B”. Use =COUNTIFS(A2:A100,"Sales",B2:B100,"A") + COUNTIFS(A2:A100,"Sales",B2:B100,"B").

Example 3: Count cells that contain the word “pending” anywhere in a text column. Use =COUNTIFS(A2:A100,"*pending*"). The asterisks act as wildcards.

Example 4: Count rows where a date is between two dates. Use =COUNTIFS(C2:C100,">="&DATE(2024,1,1),C2:C100,"<="&DATE(2024,12,31)).

Performance Tips for Large Data Sets

If your data has thousands of rows, COUNTIFS is generally fast, but you can optimize by limiting the range to the actual data area. For example, if you have 10,000 rows, use A2:A10001 instead of A:A. Also, avoid using entire column references in multiple criteria because each column reference adds processing time.

For very large datasets (over 100,000 rows), consider using Excel Tables. When you format data as a table (Ctrl+T), you can use structured references like =COUNTIFS(Table1[Region],"West",Table1[Sales],">100"). Structured references are easier to read and automatically adjust as you add rows.

If you need to count across multiple sheets, you can use 3D references with COUNTIFS? No—COUNTIFS does not support 3D references directly. You would need to use SUMPRODUCT with INDIRECT or a helper column that combines data from all sheets.

Frequently Asked Questions

Can COUNTIFS count cells that meet any of several criteria (OR logic)? No, COUNTIFS only applies AND logic. To count OR conditions, either add multiple COUNTIFS formulas together or use SUMPRODUCT with addition inside the array.

Why does my COUNTIFS return #VALUE!? Usually because your ranges have different sizes. Check that each range has the same number of rows and columns. Also ensure you haven't used a whole column in one range and a partial range in another.

How do I count cells that are not blank with multiple criteria? Use "<>" as the criteria. For example, =COUNTIFS(A2:A100,"<>",B2:B100,">0") counts rows where A is not blank and B is greater than 0.

Can I use a cell reference as the criteria without quotes? Yes, just put the cell reference directly. For example, =COUNTIFS(A2:A100,E1) uses the value in E1 as the criteria. For comparison operators, you need to concatenate: =COUNTIFS(B2:B100,">"&E1).

Does COUNTIFS work with dates? Yes, but you must enter dates as date values or use DATE function. For example, =COUNTIFS(C2:C100,">"&DATE(2024,1,1)). If you type a date as text like "1/1/2024", Excel may not interpret it correctly depending on regional settings.

What is the difference between COUNTIF and COUNTIFS? COUNTIF takes a single range and a single criteria. COUNTIFS takes multiple range/criteria pairs and applies AND logic. You can use COUNTIF for one condition, but for two or more, use COUNTIFS.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.