Prem Rawat

Peace is Possible

— Prem Rawat

True peace begins within you. Discover the peace that already exists inside yourself, and let it bring more love, happiness and understanding into your life.

Visit YouTube Channel

How to Use COUNTIFS Function in Excel (With Examples)

Learn how to use the COUNTIFS function in Excel with practical examples. Count cells using multiple criteria, dates, text, numbers, AND and OR conditi

 

How to Use COUNTIFS Function in Excel (With Examples)

   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

=COUNTIFS(C2:C7,">5000")

Less than 5000

=COUNTIFS(C2:C7,"<5000")

Equal to 5000

=COUNTIFS(C2:C7,5000)

Greater than or equal to 5000

=COUNTIFS(C2:C7,">=5000")

Less than or equal to 5000

=COUNTIFS(C2:C7,"<=5000")

Not equal to 5000

=COUNTIFS(C2:C7,"<>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

Apple*

?

Matches exactly one character

A?ple

~

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

=COUNTIFS(B2:B100,"North",C2:C100,"Completed")

Count employees earning above $4,000

=COUNTIFS(C2:C100,">4000")

Count students who passed in Science

=COUNTIFS(B2:B100,"Science",C2:C100,"Pass")

Count products with low stock in a category

=COUNTIFS(B2:B100,"Electronics",C2:C100,"<10")

Count sales during January 2026

=COUNTIFS(A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2026,2,1))

Count orders excluding Pending and blank

=COUNTIFS(C2:C100,"<>Pending",C2:C100,"<>")

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

Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content