COUNTIFS Function in Excel Tutorial

Excel Tutorial

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.

ArgumentMeaning
criteria_range1First column or range Excel checks
criteria1First condition
criteria_range2Second column or range Excel checks
criteria2Second condition

You can add more condition-range and condition pairs.

How Does Excel Evaluate Multiple Conditions?

Suppose we have these employee records:

EmployeeDepartmentSalary
AmitIT35000
NehaHR30000
RahulIT25000
PriyaIT40000

Our requirement is to count employees who work in IT and earn more than ₹30,000.

Excel checks each employee:

EmployeeIT Department?Salary > 30000?Counted?
AmitTRUETRUEYes
NehaFALSEFALSENo
RahulTRUEFALSENo
PriyaTRUETRUEYes

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.

OperatorMeaningExample
=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 IDEmployeeDepartmentSalary
E101AmitIT35000
E102NehaHR30000
E103RahulIT25000
E104PriyaIT40000
E105KaranSales45000
E106SimranIT32000

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:C7 tells Excel to check the Department column.
  • "IT" is the first condition.
  • D2:D7 tells 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 IDRegionProductSales
O101NorthLaptop50000
O102SouthMouse12000
O103NorthLaptop45000
O104NorthKeyboard18000
O105SouthLaptop60000
O106NorthMouse10000
O107NorthLaptop40000

Excel Formula

=COUNTIFS(B2:B8,"North",C2:C8,"Laptop")

Expected Result

<text color=”success” weight=”semibold”>3 Orders</text>

Explanation

Excel checks two conditions:

  1. Region must be North.
  2. 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

StudentExcel MarksPower BI Marks
Amit8575
Neha3580
Rahul6530
Priya4555
Karan9085
Simran3835

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

EmployeeDepartmentSalary
AmitIT35000
NehaHR40000
RahulSales45000
PriyaIT28000
KaranFinance50000
SimranHR32000

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 IDOrder DateProductSales
O10105-Jan-2026Laptop50000
O10210-Jan-2026Mouse12000
O10315-Jan-2026Laptop45000
O10420-Jan-2026Laptop30000
O10505-Feb-2026Laptop60000
O10625-Jan-2026Mouse15000
O10730-Jan-2026Laptop40000

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:

  1. Product must be Laptop.
  2. Order date must be on or after 1 January 2026.
  3. 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

EmployeeDepartmentSalary
AmitIT35000
NehaHR30000
RahulIT45000
PriyaSales40000
KaranIT28000
SimranHR42000

Enter these conditions:

CellValue
E2IT
F230000

Excel Formula

=COUNTIFS(B2:B7,E2,C2:C7,">"&F2)

Expected Result

<text color=”success” weight=”semibold”>2 Employees</text>

Explanation

  • E2 contains the department condition.
  • F2 contains the minimum salary value.
  • ">"&F2 tells 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 IDEmployee NameDepartment
E101AmitIT
E102NehaHR
E103AnkitIT
E104PriyaIT
E105AmanSales
E106ArjunIT
E107RahulIT

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:

  1. All conditions must match: COUNTIFS uses AND logic. One failed condition means that row is not counted.
  2. 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.
  3. Text criteria need quotation marks: For example, "IT" or "North".
  4. Comparison criteria need quotation marks: For example, ">30000" or "<=50000".
  5. Cell references can be used: You can refer to a cell containing the condition instead of typing the condition directly.
  6. 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.

  1. Count employees working in the Sales department with a salary greater than ₹35,000.
  2. Count Laptop orders received from the South region.
  3. Count students who scored at least 60 marks in both Excel and Power BI.
  4. Count employees who do not belong to the IT department and earn less than ₹40,000.
  5. 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 IDEmployeeDepartmentRegionSales
E101AmitITNorth45000
E102NehaHRSouth30000
E103RahulSalesNorth55000
E104PriyaITEast40000
E105KaranSalesNorth25000
E106SimranHRNorth42000
E107RohitITSouth60000
E108AnkitSalesEast35000
E109AmanITNorth50000
E110ArjunSalesSouth48000
E111RiyaHREast38000
E112MohitITNorth28000

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.