Discover how to use the COUNTIFS function in Excel to count cells based on multiple conditions. Learn its syntax, practical formulas, date criteria, wildcard characters, AND and OR logic, and common errors with easy examples.
How to Use the COUNTIFS Function in Excel: A Complete Guide with Practical Examples
Working with large datasets in Excel often means finding specific information that meets multiple conditions. For example, you might want to count how many employees work in a particular department and earn more than $3,000, how many orders were placed in a specific city with a particular status, or how many sales transactions happened during a selected period.
Counting records manually can be time-consuming and increases the risk of mistakes. Fortunately, Excel has a useful function called COUNTIFS that makes this task much easier.
The COUNTIFS function counts cells or records that meet two or more conditions simultaneously. It is especially helpful when working with sales reports, employee records, inventory lists, customer databases, and financial spreadsheets.
In this guide, you will learn how the COUNTIFS function works, understand its syntax, explore practical examples, and discover how to use multiple criteria to analyze your data more efficiently.
What Is the COUNTIFS Function in Excel?
The COUNTIFS function in Excel counts the number of cells that satisfy multiple conditions across one or more ranges.
Unlike the COUNTIF function, which checks only one condition, COUNTIFS allows you to apply several conditions in a single formula.
For example, imagine you have a sales report containing employee names, regions, product categories, and sales amounts. You want to count how many sales transactions were completed by employees in the North region for the Electronics category.
Instead of filtering the data and counting each matching row manually, you can use one COUNTIFS formula.
Excel COUNTIFS Formula:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)The function checks every specified condition and counts only the records that satisfy all of them.
COUNTIFS Function Syntax and Arguments
Before using COUNTIFS, it is important to understand its arguments. Each argument tells Excel where to look and what condition to check.
Syntax:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)Here is what each argument means:
Argument | Description |
|---|---|
criteria_range1 | The first range of cells you want to evaluate. |
criteria1 | The condition that must be satisfied in the first range. |
criteria_range2 | An additional range to check. |
criteria2 | The condition to apply to the additional range. |
You can use up to 127 range-and-criteria pairs in a COUNTIFS formula.
Important: All criteria ranges must have the same number of rows and columns. Otherwise, Excel may return an incorrect result or an error.
Basic Example of the COUNTIFS Function
Let's start with a simple example to understand how COUNTIFS works.
Suppose you have the following sales data:
Employee | Department | Sales |
|---|---|---|
John | Sales | 5000 |
Sarah | Marketing | 3500 |
David | Sales | 4500 |
Emma | HR | 3000 |
Michael | Sales | 6000 |
Olivia | Marketing | 4000 |
Now, you want to count how many employees belong to the Sales department and have sales greater than $4,000.
Use this formula:
=COUNTIFS(B2:B7,"Sales",C2:C7,">4000")Result: 3
Excel checks both conditions:
Department must be Sales.
Sales amount must be greater than $4,000.
John, David, and Michael meet both requirements, so the formula returns 3.
This is the main advantage of COUNTIFS: it checks multiple conditions together without requiring separate formulas.
How to Use COUNTIFS with Multiple Criteria
One of the most useful features of COUNTIFS is its ability to evaluate multiple conditions simultaneously.
Let's explore some practical situations where this function can save time.
Count Cells Based on Two Conditions
Suppose you maintain a student performance sheet and want to count students who belong to the Science department and have passed their exams.
Student | Department | Result |
|---|---|---|
Alex | Science | Pass |
Sophia | Commerce | Pass |
Daniel | Science | Fail |
Emily | Science | Pass |
James | Arts | Pass |
Use this formula:
=COUNTIFS(B2:B6,"Science",C2:C6,"Pass")Result: 2
The formula counts only students who meet both conditions: Science department and Pass result.
Count Cells Greater Than a Specific Number
COUNTIFS can also count numerical values based on comparison operators.
For example, imagine you want to count employees whose monthly sales exceed $5,000.
Use:
=COUNTIFS(C2:C7,">5000")Result: 1
Only Michael has sales greater than $5,000.
You can also use other comparison operators:
Condition | Formula Example |
|---|---|
Greater than 5000 |
|
Less than 5000 |
|
Equal to 5000 |
|
Greater than or equal to 5000 |
|
Less than or equal to 5000 |
|
Not equal to 5000 |
|
These operators make it easier to analyze sales figures, expenses, marks, quantities, and other numerical information.
Count Cells Between Two Numbers
Sometimes you may need to count values that fall within a specific range.
For example, suppose you want to count sales transactions between $3,000 and $5,000.
Use this formula:
=COUNTIFS(C2:C7,">=3000",C2:C7,"<=5000")Result: 3
The formula counts values that are at least $3,000 and no more than $5,000.
You can use this approach to calculate how many products fall within a particular price range or how many employees earn salaries within a specified range.
Count Cells That Contain Specific Text
COUNTIFS can count records based on text conditions as well.
For example, suppose you have an order management spreadsheet and want to count all orders with the status "Completed" in the North region.
Use:
=COUNTIFS(B2:B100,"North",C2:C100,"Completed")The formula counts only the records where the region is North and the order status is Completed.
This is particularly useful for tracking completed orders, customer requests, employee departments, and project statuses.
Count Cells That Are Not Equal to Specific Text
You can also exclude particular text values using the not-equal operator (<>).
For example, to count all orders that are not marked as Pending, use:
=COUNTIFS(C2:C100,"<>Pending")This counts cells that contain any value other than Pending, including other text values.
Note: Blank cells are also not equal to "Pending" and may be included in the count. If you want to exclude blank cells too, use:
=COUNTIFS(C2:C100,"<>Pending",C2:C100,"<>")Count Cells Using Wildcard Characters
Excel supports wildcard characters in COUNTIFS, allowing you to count text that matches a particular pattern.
There are three useful wildcard characters:
Wildcard | Meaning | Example |
|---|---|---|
| Matches any number of characters |
|
| Matches exactly one character |
|
| Treats a wildcard as a literal character |
|
For example, suppose a product list contains:
Apple Juice
Apple Laptop
Banana
Apple Phone
Orange Juice
To count products whose names begin with "Apple", use:
=COUNTIFS(A2:A6,"Apple*")Result: 3
The asterisk allows Excel to match any characters after the word Apple.
You can also count names containing a specific word:
=COUNTIFS(A2:A100,"*Phone*")This counts cells containing the word "Phone" anywhere in their text.
Count Cells Based on Dates
COUNTIFS is particularly useful for working with dates.
Suppose you have an order list and want to count how many orders were placed during January 2026.
Use:
=COUNTIFS(A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2026,2,1))This formula counts dates from January 1 through January 31, 2026.
Using the less-than condition for February 1 ensures that all dates and times within January are included.
You can also count records from a specific date:
=COUNTIFS(A2:A100,DATE(2026,1,15))This counts cells containing the specified date without an additional time component.
Count Cells Using Criteria from Another Cell
You don't always need to type conditions directly into your formula. You can reference another cell instead.
For example, suppose:
E2 contains the department name.
F2 contains the minimum sales amount.
Use:
=COUNTIFS(B2:B100,E2,C2:C100,">"&F2)If E2 contains Sales and F2 contains 4000, Excel counts records where the department is Sales and sales are greater than $4,000.
This approach is useful when creating interactive reports and dashboards because you can change the criteria without editing the formula.
How to Use COUNTIFS with AND and OR Conditions
Understanding the difference between AND and OR conditions is important when working with COUNTIFS.
Using AND Conditions
COUNTIFS uses AND logic by default.
This means a record must satisfy every specified condition to be included in the result.
For example:
=COUNTIFS(B2:B100,"Sales",C2:C100,">5000")Excel counts only records where the department is Sales AND the sales amount is greater than $5,000.
Using OR Conditions
If you want to count records that meet either of two conditions, you can add multiple COUNTIFS formulas.
For example, to count orders from either the North or South region:
=COUNTIFS(B2:B100,"North")+COUNTIFS(B2:B100,"South")This counts all matching North and South records.
You can also use an array constant with SUM:
=SUM(COUNTIFS(B2:B100,{"North","South"}))Both approaches are useful when you need to count records matching alternative conditions.
Important: When combining OR conditions across multiple criteria ranges, make sure your formulas reflect the exact logic you want. Simply adding COUNTIFS formulas can double-count records if the conditions overlap.
How to Use COUNTIFS with Multiple Criteria in the Same Column
Sometimes you need to count values in the same column that meet different conditions.
For example, suppose you want to count sales transactions below $2,000 or above $5,000.
You can use:
=COUNTIFS(C2:C100,"<2000")+COUNTIFS(C2:C100,">5000")The formula counts both groups and adds the results.
This technique is useful when analyzing different sales categories, price ranges, performance levels, or transaction values.
COUNTIFS vs COUNTIF: What Is the Difference?
Both functions are designed to count cells based on conditions, but they serve different purposes.
Feature | COUNTIF | COUNTIFS |
|---|---|---|
Number of conditions | One | Multiple |
Multiple criteria ranges | No | Yes |
AND logic | Not directly | Built in |
Wildcard support | Yes | Yes |
Date criteria | Yes | Yes |
Maximum criteria pairs | 1 | 127 |
COUNTIF example:
=COUNTIF(A2:A100,"Completed")Counts cells containing Completed.
COUNTIFS example:
=COUNTIFS(A2:A100,"Completed",B2:B100,"North")Counts records where the status is Completed and the region is North.
Use COUNTIF when you need to check one condition. Choose COUNTIFS when your calculation depends on two or more conditions.
Common COUNTIFS Errors and How to Fix Them
Although COUNTIFS is straightforward, a few common mistakes can cause unexpected results.
Problem | Possible Reason | Solution |
|---|---|---|
Result is 0 | Criteria do not match the data | Check spelling, spaces, and cell values |
Incorrect count | Wrong criteria range | Verify the selected ranges |
#VALUE! error | Ranges have different dimensions | Make all criteria ranges the same size |
Date count is incorrect | Dates stored as text | Convert text into valid Excel dates |
Formula does not recognize a condition | Missing quotation marks or incorrect operators | Check the criteria syntax |
Blank cells are included | Criteria allow blank values | Add an additional nonblank condition |
Here are a few additional tips to avoid mistakes:
Use quotation marks around text criteria, such as
"Sales".Use quotation marks around comparison expressions, such as
">5000".Join comparison operators with cell references using
&.Check for extra spaces in imported data.
Make sure date values are stored as actual Excel dates.
Keep all criteria ranges aligned and equal in size.
Practical Uses of the COUNTIFS Function in Excel
COUNTIFS is useful in many everyday spreadsheet tasks. Here are some practical situations where it can help.
Business Task | Example Formula |
|---|---|
Count completed orders in North |
|
Count employees earning above $4,000 |
|
Count students who passed in Science |
|
Count products with low stock in a category |
|
Count sales during January 2026 |
|
Count orders excluding Pending and blank |
|
These examples show how COUNTIFS can simplify reporting and help you analyze information without manually filtering and counting rows.
Frequently Asked Questions About the COUNTIFS Function
What Does the COUNTIFS Function Do in Excel?
COUNTIFS counts the number of cells or records that satisfy multiple conditions simultaneously. It is useful for analyzing data based on different criteria.
How Many Conditions Can COUNTIFS Handle?
Excel supports up to 127 range-and-criteria pairs in a single COUNTIFS formula.
What Is the Difference Between COUNTIF and COUNTIFS?
COUNTIF checks one condition, while COUNTIFS checks multiple conditions. COUNTIFS is more suitable for complex data analysis.
Can COUNTIFS Work with Dates?
Yes. COUNTIFS can count dates based on specific days, months, years, or date ranges using comparison operators.
Does COUNTIFS Support OR Conditions?
COUNTIFS uses AND logic by default. To apply OR logic, you can combine multiple COUNTIFS formulas using addition or use an array constant with SUM.
Final Thoughts
The COUNTIFS function is one of the most useful Excel functions for anyone who regularly works with structured data. It helps you count records based on multiple conditions without manually filtering rows or creating complicated calculations.
Whether you are managing sales reports, tracking employee performance, analyzing customer orders, or preparing financial summaries, COUNTIFS can make your spreadsheet work faster and more accurate.
Start with simple two-condition formulas and gradually experiment with dates, comparison operators, wildcards, and cell references. Once you understand how these combinations work, you can use COUNTIFS to build more powerful Excel reports and dashboards.

COMMENTS