Introduction
The COUNTIF function in Excel counts the number of cells that match a specific condition. It is useful for preparing employee reports, counting products, analysing sales records, and checking business data. Instead of manually counting matching entries, you can use COUNTIF to get the result automatically.
What is the COUNTIF Function in Excel?
COUNTIF is a conditional counting function that counts cells based on one condition.
For example:
- Count employees working in the HR department.
- Count how many times a product appears in a sales report.
- Count sales transactions from a particular region.
- Count students who scored more than 80 marks.
COUNTIF Function Syntax
=COUNTIF(range, criteria)
Arguments:
range: The cells Excel checks.criteria: The condition that determines which cells should be counted.
Practical Examples of COUNTIF Function in Excel
Question 1: Count Employees Department-Wise
Problem Statement
An organization maintains an employee list. The HR manager wants to count how many employees work in the IT department.
Employee Data
| Employee ID | Employee Name | Department |
|---|---|---|
| E101 | Amit | IT |
| E102 | Neha | HR |
| E103 | Rahul | IT |
| E104 | Priya | Sales |
| E105 | Karan | IT |
| E106 | Simran | HR |
| E107 | Rohit | IT |
Excel Formula
=COUNTIF(C2:C8,"IT")
Expected Result
| Department | Employee Count |
|---|---|
| IT | 4 |
Explanation
Excel checks the Department column and counts every cell containing IT.
Concept Covered: Department-wise employee counting.
Question 2: Count Product-Wise Sales Records
Problem Statement
A retail company wants to count how many times Laptop appears in its sales records.
Sales Data
| Order ID | Product | Sales |
|---|---|---|
| O101 | Laptop | 50000 |
| O102 | Mouse | 12000 |
| O103 | Laptop | 45000 |
| O104 | Keyboard | 18000 |
| O105 | Laptop | 60000 |
| O106 | Mouse | 10000 |
| O107 | Laptop | 40000 |
Excel Formula
=COUNTIF(B2:B8,"Laptop")
Expected Result
| Product | Number of Orders |
|---|---|
| Laptop | 4 |
Explanation
Excel checks the Product column and counts the cells containing Laptop.
Concept Covered: Product-wise record counting.
Question 3: Count Sales Transactions Greater Than ₹20,000
Problem Statement
A sales manager wants to count how many transactions have a sales value greater than ₹20,000.
Sales Data
| Order ID | Sales |
|---|---|
| O101 | 15000 |
| O102 | 25000 |
| O103 | 35000 |
| O104 | 18000 |
| O105 | 40000 |
| O106 | 12000 |
| O107 | 30000 |
Excel Formula
=COUNTIF(B2:B8,">20000")
Expected Result
| Condition | Transaction Count |
|---|---|
| Greater than 20000 | 4 |
Explanation
Excel checks each sales amount and counts only the values greater than ₹20,000.
Concept Covered: Counting values using a greater-than condition.
Question 4: Count Students Who Passed
Problem Statement
A training institute wants to count how many students scored at least 40 marks.
Student Data
| Student | Marks |
|---|---|
| Ankit | 85 |
| Priya | 32 |
| Rohit | 65 |
| Simran | 28 |
| Karan | 45 |
| Neha | 90 |
| Aman | 38 |
Excel Formula
=COUNTIF(B2:B8,">=40")
Expected Result
| Condition | Student Count |
|---|---|
| Marks greater than or equal to 40 | 5 |
Explanation
Excel counts all marks that are greater than or equal to 40.
Concept Covered: Counting records that meet a minimum requirement.
Question 5: Count Employees by Selected Department
Problem Statement
An HR manager wants to create a flexible report. The department name will be entered in a separate cell, and the employee count should update according to the selected department.
Employee Data
| Employee ID | Employee Name | Department |
|---|---|---|
| E101 | Amit | IT |
| E102 | Neha | HR |
| E103 | Rahul | IT |
| E104 | Priya | Sales |
| E105 | Karan | IT |
| E106 | Simran | HR |
| E107 | Rohit | IT |
Enter HR in E2.
Excel Formula
=COUNTIF(C2:C8,E2)
Expected Result
| Selected Department | Employee Count |
|---|---|
| HR | 2 |
Explanation
Excel uses the department entered in E2 as the condition. Change E2 to IT or Sales to count employees in another department.
Concept Covered: Using a cell reference as the COUNTIF condition.
Question 6: Count Employee Names Starting With a Specific Letter
Problem Statement
An organization wants to count employees whose names start with the letter A.
Employee Data
| Employee ID | Employee Name |
|---|---|
| E101 | Amit |
| E102 | Neha |
| E103 | Ankit |
| E104 | Priya |
| E105 | Aman |
| E106 | Rahul |
| E107 | Arjun |
Excel Formula
=COUNTIF(B2:B8,"A*")
Expected Result
| Condition | Employee Count |
|---|---|
| Names starting with A | 4 |
Explanation
The asterisk (*) is a wildcard that represents any number of characters. The condition "A*" counts names that begin with A.
Concept Covered: Using wildcards to count text entries.
Question 7: Count Blank Cells in a Report
Problem Statement
A sales manager wants to count how many employee sales records are still blank.
Sales Data
| Employee | Sales |
|---|---|
| Amit | 25000 |
| Neha | |
| Rahul | 30000 |
| Priya | |
| Karan | 40000 |
| Simran | |
| Rohit | 20000 |
Excel Formula
=COUNTIF(B2:B8,"")
Expected Result
| Description | Blank Cell Count |
|---|---|
| Missing Sales Entries | 3 |
Explanation
The condition "" tells Excel to count blank cells in the selected range.
Concept Covered: Counting blank cells.
Student Practice Tasks
Solve these tasks using only the COUNTIF function.
- Count how many employees belong to the Sales department.
- Count how many products are Mouse in the product list.
- Count sales transactions greater than ₹30,000.
- Count students who scored below 40 marks.
- Count employee names starting with the letter R.
Final Student Challenge: Build an Employee Summary Report
An organization maintains the following employee records. Prepare a summary report using COUNTIF.
Employee Data
| Employee ID | Employee Name | Department | Salary |
|---|---|---|---|
| E101 | Amit | IT | 35000 |
| E102 | Neha | HR | 30000 |
| E103 | Rahul | Sales | 40000 |
| E104 | Priya | IT | 32000 |
| E105 | Karan | HR | 28000 |
| E106 | Simran | Sales | 45000 |
| E107 | Rohit | IT | 38000 |
| E108 | Ankit | Finance | 42000 |
| E109 | Aman | HR | 31000 |
| E110 | Arjun | Sales | 36000 |
Challenge Requirements
Prepare the following reports:
1. Department-Wise Employee Count
| Department | Employee Count |
|---|---|
| IT | |
| HR | |
| Sales | |
| Finance |
2. Salary-Based Employee Count
Count employees whose salary is greater than ₹35,000.
3. Name-Based Employee Count
Count employees whose names start with the letter A.
Use COUNTIF to complete all three reports.
Key Takeaways
- COUNTIF counts cells based on one condition.
- It can count text entries and numerical values.
- It supports conditions such as greater than, less than, and equal to.
- You can use cell references as criteria.
- Wildcards such as
*help count matching text patterns. - COUNTIF can also count blank cells.
Frequently Asked Questions (FAQs)
1. What is the COUNTIF function in Excel?
COUNTIF is an Excel function that counts cells matching a specified condition.
2. What is the syntax of COUNTIF?
The syntax is =COUNTIF(range, criteria).
3. Can COUNTIF count text values?
Yes. COUNTIF can count matching text such as IT, HR, Laptop, or North.
4. Can COUNTIF count values greater than a specific number?
Yes. You can use a condition such as ">20000" to count values greater than 20,000.
5. Can COUNTIF count blank cells?
Yes. Use =COUNTIF(B2:B10,"") to count blank cells in a range.
6. Can COUNTIF count names starting with a specific letter?
Yes. You can use a wildcard condition such as "A*" to count names starting with A.
7. Can COUNTIF use a cell reference as criteria?
Yes. You can enter a condition in a separate cell and use that cell reference in the formula.
8. Is COUNTIF suitable for multiple conditions?
COUNTIF is designed for one condition. It counts cells that match the specified single criterion.

