COUNTIFS Function in Excel: Complete Tutorial with Examples

Need to count cells that meet multiple criteria? COUNTIFS tests multiple conditions simultaneously and returns how many rows match all of them. One formula handles complex counting that would otherwise require helper columns or filtering.

What Makes COUNTIFS Useful

COUNTIFS handles sophisticated counting tasks:

• Multiple criteria – Test up to 127 different conditions at once
• Different columns – Each condition can check a different column
• Flexible operators – Use equals, greater than, less than, or text matching
• Business intelligence – Count qualified leads, eligible customers, or flagged items
• Date filtering – Perfect for counting records within specific time periods

The Syntax

=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2], ...)

Pairs of ranges and criteria. All conditions must be true for a row to count.

Example 1: Regional Performance Count

Count sales over $1,000 in the West region:

Formula: =COUNTIFS(B2:B6, "West", C2:C6, ">1000")

Result: 2

Laptop and Monitor meet both criteria. Keyboard and Tablet are West but under $1,000.

Example 2: Lead Qualification

Count leads with revenue potential over $50K AND status “Hot”:

Formula: =COUNTIFS(B2:B5, ">50000", C2:C5, "Hot")

Result: 2

ABC Corp and Tech Co qualify. XYZ Inc is warm, Sales Ltd is under $50K.

Example 3: Date Range Counting

Count orders placed in January 2025:

Formula: =COUNTIFS(B2:B6, ">=1/1/2025", B2:B6, "<2/1/2025")

Result: 3

Use the same range twice with different criteria to create a date range.

Example 4: Inventory Management

Count products with stock below 20 AND reorder status “Yes”:

Formula: =COUNTIFS(B2:B6, "<20", C2:C6, "Yes")

Result: 2

Items B and C need immediate reordering. Item E is low but doesn’t need reorder. Item D needs reorder but stock is adequate.

Example 5: Performance Review Count

Count employees with sales over $100K AND rating “Excellent”:

Formula: =COUNTIFS(B2:B6, ">100000", C2:C6, "Excellent")

Result: 3

Sarah, Jennifer, and Lisa meet both criteria for top performer recognition.

Example 6: Multi-Region Analysis

Count West region sales between $500 and $2,000:

Formula: =COUNTIFS(B2:B6, "West", C2:C6, ">=500", C2:C6, "<=2000")

Result: 2

Widgets A and D fall in the target range. Widget C is too low, Widget E is too high.

Common Operators

Wildcards with COUNTIFS

Example: =COUNTIFS(A2:A10, "Sales*", B2:B10, ">1000")

Counts rows where column A starts with “Sales” AND column B exceeds 1000.

Common Mistakes to Avoid

Mismatched Range Sizes

• All ranges must be the same size
• B2:B10 works with C2:C10
• B2:B10 doesn’t work with C2:C15

Mixing COUNTIF and COUNTIFS Syntax

• COUNTIF: range, criteria
• COUNTIFS: range1, criteria1, range2, criteria2…
• Don’t confuse the order

Forgetting Quotes on Operators

• ">1000" is correct
• >1000 without quotes causes an error
• Text and operators both need quotes

Quick Tips

• Use the same range multiple times for between conditions
• Cell references don’t need quotes: COUNTIFS(A:A, B1)
• Criteria are case-insensitive for text
• Empty cells are ignored in criteria evaluation
• You can mix exact matches with operators

When to Use COUNTIFS

Use COUNTIFS when:

• You need to count with 2+ conditions
• All conditions must be true (AND logic)
• You’re analyzing subsets of data

Don’t use COUNTIFS when:

• You only have one condition (use COUNTIF)
• You need OR logic (use SUMPRODUCT instead)
• You’re counting all non-blank cells (use COUNTA)

Start Using It

Find data with categories or filters. Think of two conditions that matter together. Write a COUNTIFS formula testing both. Check if the result makes sense by manually verifying a few rows.

COUNTIFS reveals patterns in your data instantly.

Questions about COUNTIFS? Need help with complex multi-criteria counting? Let’s connect.