COUNTIF Function in Excel Tutorial

Excel Tutorial

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 IDEmployee NameDepartment
E101AmitIT
E102NehaHR
E103RahulIT
E104PriyaSales
E105KaranIT
E106SimranHR
E107RohitIT

Excel Formula

=COUNTIF(C2:C8,"IT")

Expected Result

DepartmentEmployee Count
IT4

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 IDProductSales
O101Laptop50000
O102Mouse12000
O103Laptop45000
O104Keyboard18000
O105Laptop60000
O106Mouse10000
O107Laptop40000

Excel Formula

=COUNTIF(B2:B8,"Laptop")

Expected Result

ProductNumber of Orders
Laptop4

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 IDSales
O10115000
O10225000
O10335000
O10418000
O10540000
O10612000
O10730000

Excel Formula

=COUNTIF(B2:B8,">20000")

Expected Result

ConditionTransaction Count
Greater than 200004

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

StudentMarks
Ankit85
Priya32
Rohit65
Simran28
Karan45
Neha90
Aman38

Excel Formula

=COUNTIF(B2:B8,">=40")

Expected Result

ConditionStudent Count
Marks greater than or equal to 405

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 IDEmployee NameDepartment
E101AmitIT
E102NehaHR
E103RahulIT
E104PriyaSales
E105KaranIT
E106SimranHR
E107RohitIT

Enter HR in E2.

Excel Formula

=COUNTIF(C2:C8,E2)

Expected Result

Selected DepartmentEmployee Count
HR2

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 IDEmployee Name
E101Amit
E102Neha
E103Ankit
E104Priya
E105Aman
E106Rahul
E107Arjun

Excel Formula

=COUNTIF(B2:B8,"A*")

Expected Result

ConditionEmployee Count
Names starting with A4

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

EmployeeSales
Amit25000
Neha
Rahul30000
Priya
Karan40000
Simran
Rohit20000

Excel Formula

=COUNTIF(B2:B8,"")

Expected Result

DescriptionBlank Cell Count
Missing Sales Entries3

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.

  1. Count how many employees belong to the Sales department.
  2. Count how many products are Mouse in the product list.
  3. Count sales transactions greater than ₹30,000.
  4. Count students who scored below 40 marks.
  5. 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 IDEmployee NameDepartmentSalary
E101AmitIT35000
E102NehaHR30000
E103RahulSales40000
E104PriyaIT32000
E105KaranHR28000
E106SimranSales45000
E107RohitIT38000
E108AnkitFinance42000
E109AmanHR31000
E110ArjunSales36000

Challenge Requirements

Prepare the following reports:

1. Department-Wise Employee Count

DepartmentEmployee 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.