How to Use COUNTIFS in Excel

Key Insights

COUNTIFS allows you to count cells that meet multiple criteria across different ranges, making it essential for complex data analysis where single-condition functions fall short.
The function accepts up to 127 range/criteria pairs, but matching ranges must be identical in size and shape—mismatched dimensions trigger #VALUE! errors.
Use cell references instead of hardcoded criteria to create dynamic dashboards that update automatically when conditions change, and leverage wildcards (* and ?) for flexible text matching.

Understanding COUNTIFS and When You Need It

COUNTIFS counts cells that meet multiple criteria simultaneously. While COUNT tallies numeric cells and COUNTIF handles single conditions, COUNTIFS excels at complex scenarios requiring AND logic across multiple columns.

The practical difference matters. If you need to count sales above $1000, COUNTIF works fine. But when you need sales above $1000 and from the Western region and closed in Q1, you need COUNTIFS.

Common use cases include sales pipeline analysis (count deals by stage, value, and rep), inventory management (count SKUs by category, stock level, and warehouse), and HR reporting (count employees by department, tenure, and performance rating).

COUNTIFS Syntax and Core Rules

The function follows this structure:

=COUNTIFS(range1, criteria1, [range2], [criteria2], ...)

The first range/criteria pair is required. After that, you can add up to 126 additional pairs. Each range must contain the same number of cells with the same dimensions. A 10-row by 1-column range cannot be paired with a 10-row by 2-column range.

Here’s a basic example counting sales above $1000 in Q1:

=COUNTIFS(B2:B100, ">1000", C2:C100, "Q1")

This counts rows where column B exceeds 1000 and column C equals “Q1”. Both conditions must be true for the row to count.

The ranges (B2:B100 and C2:C100) have identical dimensions—99 rows by 1 column. This is mandatory. If you accidentally use B2:B100 and C2:C101, Excel returns #VALUE!.

Working with Different Criteria Types

COUNTIFS handles text, numbers, and dates, but each requires specific syntax.

Text Criteria

For exact matches, use quotes:

=COUNTIFS(A2:A100, "North", B2:B100, "Premium")

For partial matches, use wildcards. The asterisk (*) matches any sequence of characters, while the question mark (?) matches exactly one character:

=COUNTIFS(A2:A100, "Pro*", B2:B100, "North")

This counts rows where column A starts with “Pro” (matching “Pro”, “Premium”, “Professional”) and column B equals “North”.

To match cells containing a specific substring anywhere:

=COUNTIFS(A2:A100, "*Enterprise*")

Numeric Criteria

Comparison operators must be enclosed in quotes:

=COUNTIFS(B2:B100, ">500", B2:B100, "<=2000")

This counts values in B2:B100 between 500 and 2000 (exclusive of 500, inclusive of 2000). Notice we reference the same range twice with different criteria—this is how you create range conditions.

For exact numeric matches, quotes are optional but recommended for consistency:

=COUNTIFS(C2:C100, "100")

Date Criteria

Dates require careful handling. Use DATE function for clarity:

=COUNTIFS(D2:D100, ">="&DATE(2024,1,1), D2:D100, "<"&DATE(2024,4,1))

This counts dates in Q1 2024. The ampersand (&) concatenates the operator with the date value.

Alternatively, reference cells containing dates:

=COUNTIFS(D2:D100, ">="&F1, D2:D100, "<"&F2)

Where F1 contains the start date and F2 contains the end date.

Advanced Techniques for Complex Analysis

Using Cell References for Dynamic Criteria

Hardcoded criteria create brittle formulas. Cell references make dashboards dynamic:

=COUNTIFS(A2:A100, G2, B2:B100, ">"&G3, C2:C100, G4)

Now users can change values in G2, G3, and G4 to instantly update results. This is crucial for interactive reports.

Named Ranges for Readability

Define named ranges (Formulas > Name Manager) to make formulas self-documenting:

=COUNTIFS(SalesRegion, "West", SalesAmount, ">1000", SalesDate, ">="&StartDate)

This is far clearer than cell references and easier to maintain.

Handling Blank and Non-Blank Cells

Count non-blank cells:

=COUNTIFS(A2:A100, "<>")

Count blank cells:

=COUNTIFS(A2:A100, "")

Combine with other criteria:

=COUNTIFS(A2:A100, "West", B2:B100, "<>", C2:C100, ">1000")

This counts Western region rows where column B isn’t blank and column C exceeds 1000.

Complex Multi-Criteria Scenario

Here’s a real-world example counting enterprise sales in the Western region closed in Q1 2024:

=COUNTIFS(
    A2:A500, "West",
    B2:B500, "*Enterprise*",
    C2:C500, ">="&DATE(2024,1,1),
    C2:C500, "<"&DATE(2024,4,1),
    D2:D500, "Closed Won"
)

This demonstrates multiple criteria types working together: exact text match, wildcard text match, date range (using the same range twice), and status verification.

Common Errors and How to Fix Them

#VALUE! Error

This almost always means mismatched range dimensions. Check that all ranges have identical row and column counts:

=COUNTIFS(A2:A100, "West", B2:B101, ">1000")  // Wrong - different sizes
=COUNTIFS(A2:A100, "West", B2:B100, ">1000")  // Correct

Unexpected Zero Results

Usually caused by criteria formatting issues. If column B contains numbers but you search for text:

=COUNTIFS(B2:B100, "1000")  // May return 0
=COUNTIFS(B2:B100, 1000)    // Correct for numeric data

For dates, ensure criteria use proper date serial numbers, not text strings.

Performance Optimization

COUNTIFS recalculates on every sheet change. For large datasets (10,000+ rows), minimize volatile references and consider:

Using helper columns to pre-calculate complex conditions
Limiting range sizes to actual data (avoid entire column references like A:A)
Converting static reports to values after calculation

Practical Real-World Example: Sales Dashboard

Let’s build a sales analysis dashboard. Assume data in columns A-E: Region, Product, Amount, Date, Status.

Count high-value deals by region:

// Cell G2: West region deals > $5000
=COUNTIFS($A$2:$A$1000, "West", $C$2:$C$1000, ">5000", $E$2:$E$1000, "Closed Won")

// Cell G3: East region deals > $5000
=COUNTIFS($A$2:$A$1000, "East", $C$2:$C$1000, ">5000", $E$2:$E$1000, "Closed Won")

Count deals by product tier and quarter:

// Premium products in Q1
=COUNTIFS($B$2:$B$1000, "*Premium*", $D$2:$D$1000, ">="&DATE(2024,1,1), $D$2:$D$1000, "<"&DATE(2024,4,1))

// Enterprise products in Q1
=COUNTIFS($B$2:$B$1000, "*Enterprise*", $D$2:$D$1000, ">="&DATE(2024,1,1), $D$2:$D$1000, "<"&DATE(2024,4,1))

Create a dynamic filter using dropdown cells:

// G1 contains region dropdown, G2 contains minimum amount
=COUNTIFS($A$2:$A$1000, G1, $C$2:$C$1000, ">"&G2, $E$2:$E$1000, "Closed Won")

When to Use Alternatives

SUMIFS for Value Totals

If you need sum instead of count, use SUMIFS with identical syntax:

=SUMIFS(C2:C100, A2:A100, "West", B2:B100, ">1000")

FILTER with COUNTA (Excel 365)

For dynamic arrays that display matching rows:

=COUNTA(FILTER(A2:A100, (B2:B100="West")*(C2:C100>1000)))

This is more powerful when you need to see the actual data, not just counts.

Pivot Tables

For multi-dimensional analysis with interactive filtering, pivot tables outperform formulas. Use COUNTIFS for calculated metrics within reports, but consider pivot tables for exploratory analysis.

DCOUNT for Database-Style Queries

DCOUNT uses a criteria range format that’s more readable for very complex conditions, but it’s less intuitive and rarely necessary.

The bottom line: COUNTIFS is your go-to for counting with multiple conditions. It’s fast, flexible, and works in all Excel versions. Master the syntax, use cell references for dynamic criteria, and watch for range dimension mismatches. You’ll build powerful analytical dashboards that update automatically and provide instant insights into your data.