Excel COUNTIF vs COUNTIFS Difference

If you work with data in Excel, counting records that meet specific conditions is something you will do constantly — counting how many sales were above a target, how many employees are in a specific department, or how many orders were placed in a given month.

Excel gives you two functions for this:

• COUNTIF — Count cells that meet one condition
• COUNTIFS — Count cells that meet multiple conditions simultaneously

They look nearly identical and are easy to confuse. But understanding exactly how they differ and when to use each is one of the most practical Excel skills you can develop.

In this guide, we will break down both functions clearly with step-by-step examples, real-world use cases, and practical tips.

What Is COUNTIF?

COUNTIF counts the number of cells in a range that meet a single condition.

Syntax

=COUNTIF(range, criteria)

• range — The cells you want to check
• criteria — The condition that must be met

Simple Examples

=COUNTIF(A2:A100, "Engineering")     ← Count cells equal to "Engineering"
=COUNTIF(B2:B100, ">80000")          ← Count cells greater than 80000
=COUNTIF(C2:C100, "Active")          ← Count cells equal to "Active"
=COUNTIF(D2:D100, "<>")              ← Count non-empty cells

What Is COUNTIFS?

COUNTIFS counts cells that meet two or more conditions simultaneously across one or more ranges. It is the multi-condition version of COUNTIF.

Syntax

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

• Each range-criteria pair adds another condition
• All conditions must be TRUE simultaneously for a row to be counted
• You can have up to 127 range-criteria pairs

Simple Examples

=COUNTIFS(A2:A100, "Engineering", C2:C100, "Active")
← Count Engineering employees who are also Active

=COUNTIFS(B2:B100, ">80000", D2:D100, ">=7")
← Count employees with salary above 80000 AND experience of 7 or more years

Setting Up the Example Dataset

Column A — Name:      Alice, Bob, Charlie, Diana, Eve, Frank, Grace
Column B — Dept:      Engineering, Marketing, Engineering, HR, Sales, Marketing, Engineering
Column C — Salary:    95000, 62000, 88000, 55000, 71000, 67000, 91000
Column D — Exp:       8, 4, 6, 3, 5, 7, 9
Column E — Status:    Active, Active, Inactive, Active, Inactive, Active, Active

COUNTIF in Action

Example 1: Count a Specific Text Value

How many employees work in Engineering?

=COUNTIF(B2:B8, "Engineering")

Result: 3 (Alice, Charlie, Grace)

Example 2: Count With a Numeric Condition

How many employees earn more than $80,000?

=COUNTIF(C2:C8, ">80000")

Result: 3 (Alice at 95000, Charlie at 88000, Grace at 91000)

Example 3: Count Using a Cell Reference as Criteria

Instead of hardcoding the criteria, reference a cell. Put “Engineering” in cell H2:

=COUNTIF(B2:B8, H2)

This makes your formula dynamic — change H2 and the count updates automatically.

Example 4: Using Wildcard Characters

COUNTIF supports two wildcards:

• * — matches any sequence of characters
• ? — matches any single character

=COUNTIF(B2:B8, "En*")       ← Count departments starting with "En"
=COUNTIF(A2:A8, "?????")     ← Count names with exactly 5 characters
=COUNTIF(B2:B8, "*ing")      ← Count departments ending with "ing"

Example 5: Count Non-Empty Cells

=COUNTIF(C2:C8, "<>")        ← Count cells that are not blank
=COUNTIF(C2:C8, "")          ← Count cells that ARE blank

Example 6: Count With Comparison Operators

=COUNTIF(D2:D8, ">=7")       ← Experience 7 or more years → Result: 3
=COUNTIF(C2:C8, "<=65000")   ← Salary 65000 or less → Result: 2
=COUNTIF(C2:C8, "<>88000")   ← Salary not equal to 88000 → Result: 6

COUNTIFS in Action

Example 1: Two Conditions — Text and Text

How many employees are in Engineering AND have Active status?

=COUNTIFS(B2:B8, "Engineering", E2:E8, "Active")

Result: 2 (Alice and Grace — Charlie is Inactive)

Example 2: Two Conditions — Text and Number

How many Marketing employees earn more than $65,000?

=COUNTIFS(B2:B8, "Marketing", C2:C8, ">65000")

Result: 1 (Frank at 67000 — Bob earns only 62000)

Example 3: Three Conditions

How many Engineering employees are Active AND have more than 7 years experience?

=COUNTIFS(B2:B8, "Engineering", E2:E8, "Active", D2:D8, ">7")

Result: 2 (Alice with 8 years, Grace with 9 years)

Example 4: Count a Range of Values (Between)

How many employees earn between $65,000 and $95,000?

=COUNTIFS(C2:C8, ">=65000", C2:C8, "<=95000")

Notice that the same column appears twice — once for each boundary condition. This is how you create a between filter with COUNTIFS.

Result: 5 (Alice, Charlie, Eve, Frank, Grace)

Example 5: Combining Text and Date Ranges

=COUNTIFS(B2:B8, "Engineering", F2:F8, ">="&DATE(2024,1,1), F2:F8, "<"&DATE(2025,1,1))

Count Engineering employees who joined in 2024.

Example 6: Using Cell References for Dynamic Criteria

Put “Engineering” in H2 and “Active” in H3:

=COUNTIFS(B2:B8, H2, E2:E8, H3)

Change the values in H2 or H3 and the count updates instantly — perfect for interactive dashboards.

Key Differences Between COUNTIF and COUNTIFS

Difference 1: Number of Conditions

This is the core difference.

• COUNTIF — Exactly one range and one criterion
• COUNTIFS — One or more range-criteria pairs, all evaluated simultaneously

Difference 2: Syntax Structure

COUNTIF  → (range, criteria)
COUNTIFS → (range1, criteria1, range2, criteria2, range3, criteria3, ...)

Difference 3: Can You Use COUNTIFS Instead of COUNTIF?

Yes, completely. COUNTIFS with one condition works identically to COUNTIF.

=COUNTIF(B2:B8, "Engineering")
=COUNTIFS(B2:B8, "Engineering")   ← Same result

Many experienced Excel users default to COUNTIFS for everything — one function to learn, works in all situations.

Difference 4: Range Sizes Must Match in COUNTIFS

In COUNTIFS, every range must be exactly the same size. Mismatched ranges produce a VALUE error.

=COUNTIFS(B2:B8, "Engineering", C2:C10, ">80000")
← ERROR — B2:B8 has 7 rows, C2:C10 has 9 rows

Feature	COUNTIF	COUNTIFS
Number of conditions	Exactly 1	1 or more
Multiple ranges	No	Yes
Wildcards (* and ?)	Yes	Yes
Cell reference as criteria	Yes	Yes
Between range filter	Not directly	Yes (same column twice)
Range size requirement	Single range	All ranges must match
Can replace the other	Cannot do multiple	Can always replace COUNTIF
Best for	Simple single condition counts	Most real-world use cases

Real-World Use Cases

HR Dashboard — Headcount by Department and Status

Active Engineering employees:
=COUNTIFS(B2:B100, "Engineering", E2:E100, "Active")

Inactive Marketing employees:
=COUNTIFS(B2:B100, "Marketing", E2:E100, "Inactive")

Total active employees across all departments:
=COUNTIF(E2:E100, "Active")

Sales Reporting — Orders in a Date Range

Orders in Q1 2024:
=COUNTIFS(DateColumn, ">="&DATE(2024,1,1), DateColumn, "<="&DATE(2024,3,31))

High-value orders in Q1 2024:
=COUNTIFS(DateColumn, ">="&DATE(2024,1,1), DateColumn, "<="&DATE(2024,3,31), AmountColumn, ">1000")

Inventory Management — Low Stock Alert

Products below reorder level:
=COUNTIF(StockColumn, "<50")

Products below reorder level in a specific category:
=COUNTIFS(CategoryColumn, "Electronics", StockColumn, "<50")

Survey Analysis — Response Counting

Respondents who rated satisfaction 4 or 5:
=COUNTIFS(RatingColumn, ">=4", RatingColumn, "<=5")

Female respondents aged 25-34:
=COUNTIFS(GenderColumn, "Female", AgeColumn, ">=25", AgeColumn, "<=34")

Common Mistakes to Avoid

• Forgetting quotes around text criteria — =COUNTIF(B2:B8, Engineering) causes an error. Text criteria must always be in quotes: =COUNTIF(B2:B8, "Engineering")
• Forgetting quotes around operators — =COUNTIF(C2:C8, >80000) is wrong. Operators must be inside quotes: =COUNTIF(C2:C8, ">80000")
• Mismatched range sizes in COUNTIFS — Every range in a COUNTIFS formula must be exactly the same number of rows and columns. Always double-check your ranges
• Using AND logic when you mean OR — COUNTIFS always applies AND logic. If you need OR (count rows where department is Engineering OR Sales), use two separate COUNTIF functions added together:

=COUNTIF(B2:B8, "Engineering") + COUNTIF(B2:B8, "Sales")

• Hardcoding criteria instead of referencing cells — Hardcoded criteria mean editing formulas every time something changes. Reference cells instead for flexible, maintainable formulas
• Using COUNTIF for between ranges — COUNTIF cannot check upper and lower bounds simultaneously. Use COUNTIFS with the same column twice:

Wrong:  =COUNTIF(C2:C8, ">=65000 AND <=95000")
Right:  =COUNTIFS(C2:C8, ">=65000", C2:C8, "<=95000")

Quick Reference Cheat Sheet

Task	Formula
Count one condition	=COUNTIF(range, "value")
Count greater than	=COUNTIF(range, ">value")
Count non-blank	=COUNTIF(range, "<>")
Count with wildcard	=COUNTIF(range, "En*")
Count two conditions	=COUNTIFS(r1, c1, r2, c2)
Count between values	=COUNTIFS(r, ">=low", r, "<=high")
Count three conditions	=COUNTIFS(r1, c1, r2, c2, r3, c3)
Count OR logic	=COUNTIF(r,"A")+COUNTIF(r,"B")
Dynamic criteria	=COUNTIFS(r1, H1, r2, H2)

COUNTIF and COUNTIFS are two of the most practical functions in all of Excel and once you understand their difference, you will use them constantly.

Here is the simplest way to decide which to use:

• One condition → COUNTIF or COUNTIFS (both work)
• Two or more conditions → COUNTIFS only
• Between range filter → COUNTIFS with same column twice
• OR logic → Multiple COUNTIF functions added together

Most experienced Excel users default to COUNTIFS for everything. It covers every scenario COUNTIF handles and every scenario COUNTIF cannot.

FAQs