How to Use COUNTIFS in Excel

Microsoft Excel offers a wide range of functions for analyzing data, and among them, COUNTIFS stands out as one of the most practical tools for working with large datasets. While basic counting functions like COUNT and COUNTA are useful, they fall short when you need to count values that meet multiple conditions at the same time. This is where COUNTIFS becomes essential.

In this guide, you will learn what COUNTIFS is, how it works, and how to use it effectively with real-world examples. We will cover syntax, practical use cases, advanced techniques, common mistakes, and performance tips to help you confidently apply COUNTIFS in everyday Excel tasks.

What Is the COUNTIFS Function?

The COUNTIFS function counts the number of cells that meet two or more criteria across one or more ranges. It is especially useful for analyzing structured data such as sales records, attendance sheets, survey responses, and performance metrics.

COUNTIFS is an extension of the COUNTIF function. While COUNTIF allows only one condition, COUNTIFS enables you to apply multiple conditions simultaneously.

When Should You Use COUNTIFS?

Use COUNTIFS when you need to:

• Count records that meet multiple logical conditions
• Analyze data across several columns
• Replace complex helper columns or manual filtering
• Build dynamic reports and dashboards

COUNTIFS Syntax Explained

The syntax for COUNTIFS is:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Syntax Breakdown

• criteria_range1 – The first range to evaluate
• criteria1 – The condition applied to the first range
• criteria_range2 – (Optional) The second range to evaluate
• criteria2 – The condition applied to the second range
• You can add up to 127 range/criteria pairs

⚠️ Important Rule: All criteria ranges must be the same size and shape. If the ranges don’t align, Excel will return an error.

A Simple COUNTIFS Example

Imagine you have the following dataset:

Example Goal

Count how many employees:

• Work in the Sales department
• Have Active status

Formula

=COUNTIFS(B2:B5,"Sales",C2:C5,"Active")

Result

The formula returns 2, because John and Emma meet both conditions.

Using COUNTIFS with Numbers

COUNTIFS is not limited to text values. It works very well with numerical conditions such as greater than, less than, or equal to.

Example: Count Sales Above a Threshold

Count how many employees have:

• Sales greater than 4000
• Status is Active

=COUNTIFS(D2:D5,">4000",C2:C5,"Active")

This formula counts employees who meet both numeric and text conditions.

COUNTIFS with Comparison Operators

COUNTIFS supports logical operators such as:

Example

Count employees with sales less than or equal to 3000 and status Active:

=COUNTIFS(D2:D5,"<=3000",C2:C5,"Active")

Using COUNTIFS with Dates

Dates are a very common use case for COUNTIFS, especially in reporting and analytics.

Example Dataset

Example Goal

Count how many orders were:

• In the East region
• During January 2025

=COUNTIFS(A2:A5,">=01/01/2025",A2:A5,"<=31/01/2025",B2:B5,"East")

This approach is extremely useful for monthly, quarterly, or yearly reports.

Using COUNTIFS with Cell References

Instead of hard-coding criteria, you can reference cells to make your formulas dynamic.

Example

If:

• Cell E1 contains the department name
• Cell E2 contains the status

=COUNTIFS(B2:B100,E1,C2:C100,E2)

This allows you to change the criteria simply by updating the cell values, making COUNTIFS ideal for dashboards and interactive reports.

COUNTIFS with Wildcards

COUNTIFS supports wildcards, which are useful for partial matches.

Wildcards Supported

Example: Count Names Starting with “A”

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

Example: Count Email Addresses Ending with “.com”

=COUNTIFS(B2:B100,"*.com")

COUNTIFS with “Not Equal To” Conditions

You can exclude values using the <> operator.

Example

Count employees who are:

• Not in the Sales department
• Status is Active

=COUNTIFS(B2:B100,"<>Sales",C2:C100,"Active")

This is useful when filtering out unwanted categories.

COUNTIFS Across Multiple Columns

COUNTIFS works across multiple columns but not across non-contiguous rows in a single range.

Example

Count orders that are:

• From the North region
• Greater than 50 units
• Marked as Completed

=COUNTIFS(B2:B100,"North",C2:C100,">50",D2:D100,"Completed")

All criteria must be met simultaneously.

COUNTIFS vs COUNTIF: Key Differences

If your analysis requires more than one condition, COUNTIFS is always the better choice.

Common COUNTIFS Errors and How to Fix Them

1. Mismatched Range Sizes

All criteria ranges must have the same number of rows and columns.

❌ Incorrect

=COUNTIFS(A2:A10,B2:B20,"Yes")

✅ Correct

=COUNTIFS(A2:A10,"Yes",B2:B10,"Yes")

2. Incorrect Date Formatting

Excel stores dates as numbers. If a date is stored as text, COUNTIFS may fail.

Tip: Ensure dates are properly formatted as Date values.

3. Forgetting Quotation Marks

Text criteria must be enclosed in quotation marks.

❌ Incorrect

=COUNTIFS(B2:B100,Sales)

✅ Correct

=COUNTIFS(B2:B100,"Sales")

Performance Tips for Large Datasets

• Avoid using entire columns (e.g., A:A) in large workbooks
• Use Excel Tables to auto-adjust ranges efficiently
• Minimize volatile formulas alongside COUNTIFS
• Combine COUNTIFS with PivotTables when datasets exceed hundreds of thousands of rows

Practical Business Use Cases

COUNTIFS is widely used in:

• Sales performance tracking
• Attendance and HR analysis
• Inventory management
• Financial reporting
• Customer segmentation
• Survey and research analysis

Its ability to evaluate multiple conditions simultaneously makes it indispensable for professional Excel users.

Final Thoughts

The COUNTIFS function is one of Excel’s most powerful tools for data analysis. It allows you to move beyond simple counting and perform structured, condition-based analysis with clarity and efficiency. Whether you are a beginner learning Excel fundamentals or an advanced user building reports, mastering COUNTIFS will significantly improve your workflow.

By understanding its syntax, applying practical examples, and avoiding common pitfalls, you can use COUNTIFS confidently to extract meaningful insights from your data.

If you already use COUNTIF regularly, COUNTIFS is the natural next step—and once you adopt it, you will wonder how you ever analyzed data without it.