Introduction
The COUNTIFS function in Excel counts records that satisfy multiple conditions at the same time. It is useful for employee reports, sales analysis, student results, and business data. In this tutorial, you will first understand how multiple conditions work and then practise COUNTIFS with real-world examples.
1. What is the COUNTIFS Function in Excel?
COUNTIFS is a conditional counting function.
It counts how many rows satisfy all the conditions you specify.
Let’s understand this with a simple example.
Imagine a company has 10 employees. You want to count employees who belong to the IT department.
Excel checks the Department column and counts every employee whose department is IT.
Now suppose you want to count only employees who:
- Work in the IT department.
- Have a salary greater than ₹30,000.
Excel must check both conditions for each employee. It counts an employee only when both conditions are satisfied.
This is the main purpose of COUNTIFS.
Remember: COUNTIFS uses AND logic between its conditions. Every specified condition must be satisfied.
2. COUNTIFS Function Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Let’s understand each argument.
| Argument | Meaning |
|---|---|
| criteria_range1 | First column or range Excel checks |
| criteria1 | First condition |
| criteria_range2 | Second column or range Excel checks |
| criteria2 | Second condition |
You can add more condition-range and condition pairs.
How Does Excel Evaluate Multiple Conditions?
Suppose we have these employee records:
| Employee | Department | Salary |
|---|---|---|
| Amit | IT | 35000 |
| Neha | HR | 30000 |
| Rahul | IT | 25000 |
| Priya | IT | 40000 |
Our requirement is to count employees who work in IT and earn more than ₹30,000.
Excel checks each employee:
| Employee | IT Department? | Salary > 30000? | Counted? |
|---|---|---|---|
| Amit | TRUE | TRUE | Yes |
| Neha | FALSE | FALSE | No |
| Rahul | TRUE | FALSE | No |
| Priya | TRUE | TRUE | Yes |
Expected Result: 2 employees
Here is what happened:
- Amit satisfies both conditions.
- Neha does not satisfy the IT condition.
- Rahul satisfies the IT condition but fails the salary condition.
- Priya satisfies both conditions.
Only Amit and Priya are counted.
This is how COUNTIFS evaluates multiple conditions.
3. Understanding Criteria and Comparison Operators
A criterion is simply a rule that Excel must check.
For example, "IT" means Excel should look for cells containing IT.
Comparison operators allow Excel to check numerical conditions.
| Operator | Meaning | Example |
|---|---|---|
= | Equal to | "=IT" |
> | Greater than | ">30000" |
< | Less than | "<30000" |
>= | Greater than or equal to | ">=30000" |
<= | Less than or equal to | "<=30000" |
<> | Not equal to | "<>IT" |
Understanding TRUE and FALSE
When Excel checks a condition, the result is either:
- TRUE: The condition is satisfied.
- FALSE: The condition is not satisfied.
For example:
If an employee’s salary is ₹40,000:
- Is salary greater than ₹30,000? TRUE.
- Is salary less than ₹20,000? FALSE.
- Is salary equal to ₹40,000? TRUE.
You don’t need to manually write TRUE or FALSE in COUNTIFS. Excel checks the criteria automatically.
Practical Examples of COUNTIFS Function in Excel
Question 1: Count Employees in a Department With a Minimum Salary
Problem Statement
An HR manager wants to count employees who work in the IT department and earn more than ₹30,000.
Employee Data
| Employee ID | Employee | Department | Salary |
|---|---|---|---|
| E101 | Amit | IT | 35000 |
| E102 | Neha | HR | 30000 |
| E103 | Rahul | IT | 25000 |
| E104 | Priya | IT | 40000 |
| E105 | Karan | Sales | 45000 |
| E106 | Simran | IT | 32000 |
Excel Formula
=COUNTIFS(C2:C7,"IT",D2:D7,">30000")
Expected Result
<text color=”success” weight=”semibold”>3 Employees</text>
Explanation
Let’s understand the formula:
C2:C7tells Excel to check the Department column."IT"is the first condition.D2:D7tells Excel to check the Salary column.">30000"is the second condition.
Excel counts employees only when the department is IT and salary is greater than ₹30,000.
The matching employees are Amit, Priya, and Simran.
Concept Covered: Combining a text condition and a numerical condition.
Question 2: Count Sales Transactions for a Region and Product
Problem Statement
A company wants to count how many Laptop orders were received from the North region.
Sales Data
| Order ID | Region | Product | Sales |
|---|---|---|---|
| O101 | North | Laptop | 50000 |
| O102 | South | Mouse | 12000 |
| O103 | North | Laptop | 45000 |
| O104 | North | Keyboard | 18000 |
| O105 | South | Laptop | 60000 |
| O106 | North | Mouse | 10000 |
| O107 | North | Laptop | 40000 |
Excel Formula
=COUNTIFS(B2:B8,"North",C2:C8,"Laptop")
Expected Result
<text color=”success” weight=”semibold”>3 Orders</text>
Explanation
Excel checks two conditions:
- Region must be North.
- Product must be Laptop.
O101, O103, and O107 satisfy both conditions.
Therefore, the result is 3.
Concept Covered: Counting records using two text conditions.
Question 3: Count Students Who Passed in Two Subjects
Problem Statement
A training institute wants to count students who scored at least 40 marks in both Excel and Power BI.
Student Data
| Student | Excel Marks | Power BI Marks |
|---|---|---|
| Amit | 85 | 75 |
| Neha | 35 | 80 |
| Rahul | 65 | 30 |
| Priya | 45 | 55 |
| Karan | 90 | 85 |
| Simran | 38 | 35 |
Excel Formula
=COUNTIFS(B2:B7,">=40",C2:C7,">=40")
Expected Result
<text color=”success” weight=”semibold”>3 Students</text>
Explanation
Excel checks:
- Excel marks must be at least 40.
- Power BI marks must also be at least 40.
Amit, Priya, and Karan satisfy both conditions.
Neha fails Excel, Rahul fails Power BI, and Simran fails both.
Concept Covered: Applying minimum-score conditions across two subjects.
Question 4: Count Employees Who Do Not Belong to a Department
Problem Statement
An organization wants to count employees who are not working in the HR department and earn more than ₹30,000.
Employee Data
| Employee | Department | Salary |
|---|---|---|
| Amit | IT | 35000 |
| Neha | HR | 40000 |
| Rahul | Sales | 45000 |
| Priya | IT | 28000 |
| Karan | Finance | 50000 |
| Simran | HR | 32000 |
Excel Formula
=COUNTIFS(B2:B7,"<>HR",C2:C7,">30000")
Expected Result
<text color=”success” weight=”semibold”>3 Employees</text>
Explanation
The formula contains the condition "<>HR".
The <> operator means not equal to.
Excel counts employees who:
- Do not belong to HR.
- Earn more than ₹30,000.
Amit, Rahul, and Karan satisfy both conditions.
Concept Covered: Using the not-equal-to operator with multiple conditions.
Question 5: Count Sales Transactions Within a Date Range
Problem Statement
A sales manager wants to count Laptop orders received during January 2026.
Sales Data
| Order ID | Order Date | Product | Sales |
|---|---|---|---|
| O101 | 05-Jan-2026 | Laptop | 50000 |
| O102 | 10-Jan-2026 | Mouse | 12000 |
| O103 | 15-Jan-2026 | Laptop | 45000 |
| O104 | 20-Jan-2026 | Laptop | 30000 |
| O105 | 05-Feb-2026 | Laptop | 60000 |
| O106 | 25-Jan-2026 | Mouse | 15000 |
| O107 | 30-Jan-2026 | Laptop | 40000 |
Excel Formula
=COUNTIFS(C2:C8,"Laptop",B2:B8,">="&DATE(2026,1,1),B2:B8,"<="&DATE(2026,1,31))
Expected Result
<text color=”success” weight=”semibold”>3 Orders</text>
Explanation
Excel checks three conditions:
- Product must be Laptop.
- Order date must be on or after 1 January 2026.
- Order date must be on or before 31 January 2026.
The matching orders are O101, O103, and O104.
The & symbol joins the comparison operator with the date value so Excel can use it as a criterion.
Concept Covered: Counting records within a date range.
Question 6: Count Employees Using Cell References
Problem Statement
An HR manager wants to create a flexible employee report. The user will enter a department and minimum salary in separate cells.
Employee Data
| Employee | Department | Salary |
|---|---|---|
| Amit | IT | 35000 |
| Neha | HR | 30000 |
| Rahul | IT | 45000 |
| Priya | Sales | 40000 |
| Karan | IT | 28000 |
| Simran | HR | 42000 |
Enter these conditions:
| Cell | Value |
|---|---|
| E2 | IT |
| F2 | 30000 |
Excel Formula
=COUNTIFS(B2:B7,E2,C2:C7,">"&F2)
Expected Result
<text color=”success” weight=”semibold”>2 Employees</text>
Explanation
E2contains the department condition.F2contains the minimum salary value.">"&F2tells Excel to count salaries greater than the value in F2.
Amit and Rahul satisfy both conditions.
Change E2 or F2 to calculate a different employee count.
Concept Covered: Creating a flexible report using cell references.
Question 7: Count Records With a Specific Text Pattern
Problem Statement
An organization wants to count employees whose names start with the letter A and belong to the IT department.
Employee Data
| Employee ID | Employee Name | Department |
|---|---|---|
| E101 | Amit | IT |
| E102 | Neha | HR |
| E103 | Ankit | IT |
| E104 | Priya | IT |
| E105 | Aman | Sales |
| E106 | Arjun | IT |
| E107 | Rahul | IT |
Excel Formula
=COUNTIFS(B2:B8,"A*",C2:C8,"IT")
Expected Result
<text color=”success” weight=”semibold”>3 Employees</text>
Explanation
The formula checks two conditions:
- Employee name must start with A.
- Department must be IT.
The asterisk (*) is a wildcard that represents any number of characters.
Amit, Ankit, and Arjun satisfy both conditions.
Concept Covered: Using wildcards with multiple conditions.
5. Important Rules to Remember
Before using COUNTIFS in your worksheets, remember these rules:
- All conditions must match: COUNTIFS uses AND logic. One failed condition means that row is not counted.
- Ranges should have the same size: If the first criteria range contains 10 rows, the other criteria ranges should also contain the corresponding 10 rows.
- Text criteria need quotation marks: For example,
"IT"or"North". - Comparison criteria need quotation marks: For example,
">30000"or"<=50000". - Cell references can be used: You can refer to a cell containing the condition instead of typing the condition directly.
- COUNTIFS counts matching records: It does not add salaries, sales, or other numerical values.
Student Practice Tasks
Use the COUNTIFS function to solve these tasks.
- Count employees working in the Sales department with a salary greater than ₹35,000.
- Count Laptop orders received from the South region.
- Count students who scored at least 60 marks in both Excel and Power BI.
- Count employees who do not belong to the IT department and earn less than ₹40,000.
- Count January sales transactions for the North region where the sales amount is greater than ₹20,000.
Final Student Challenge: Build a Company Performance Report
A company maintains the following employee and sales records. Prepare a summary report using COUNTIFS.
Company Data
| Employee ID | Employee | Department | Region | Sales |
|---|---|---|---|---|
| E101 | Amit | IT | North | 45000 |
| E102 | Neha | HR | South | 30000 |
| E103 | Rahul | Sales | North | 55000 |
| E104 | Priya | IT | East | 40000 |
| E105 | Karan | Sales | North | 25000 |
| E106 | Simran | HR | North | 42000 |
| E107 | Rohit | IT | South | 60000 |
| E108 | Ankit | Sales | East | 35000 |
| E109 | Aman | IT | North | 50000 |
| E110 | Arjun | Sales | South | 48000 |
| E111 | Riya | HR | East | 38000 |
| E112 | Mohit | IT | North | 28000 |
Challenge Requirements
1. Department and Region-Wise Employee Count
Count IT employees working in the North region.
2. Department and Salary-Based Count
Count Sales employees whose sales amount is greater than ₹40,000.
3. Region and Sales-Based Count
Count employees in the North region whose sales are at least ₹40,000.
4. Multiple Condition Report
Count IT employees in the North region whose sales are greater than ₹30,000.
Try to solve all four tasks without looking at the answers first.
Key Takeaways
- COUNTIFS counts records using multiple conditions.
- All conditions must be satisfied for a record to be counted.
- It follows AND logic between criteria.
- You can use text, numbers, comparison operators, dates, and wildcards.
- Cell references make reports flexible and easier to update.
- COUNTIFS counts matching cells; it does not calculate their total.
Frequently Asked Questions (FAQs)
1. What is the COUNTIFS function in Excel?
COUNTIFS is a function that counts the number of records satisfying multiple specified conditions.
2. What is the difference between COUNTIF and COUNTIFS?
COUNTIF checks one condition, while COUNTIFS checks multiple conditions at the same time.
3. Does COUNTIFS use AND or OR logic?
COUNTIFS uses AND logic between its criteria. Every condition must be satisfied for a row to be counted.
4. Can COUNTIFS count employees from a specific department with a minimum salary?
Yes. You can check the Department column and Salary column using two separate conditions.
5. Can COUNTIFS work with dates?
Yes. You can count records between two dates by using date comparison conditions.
6. What does <> mean in COUNTIFS?
The <> operator means not equal to. For example, "<>HR" counts cells that are not equal to HR.
7. Can COUNTIFS use cell references?
Yes. You can enter conditions in separate cells and use those cell references in the formula.
8. Can COUNTIFS count names starting with a particular letter?
Yes. You can use a wildcard such as "A*" to match names starting with A.

