Microsoft Excel Tutorial - How to use Countif function

The Microsoft Excel COUNTIF function counts the number of cells in a range, that meets a given criteria.

Note: That COUNTIF can count both numbers and text. Unlike count function which counts only numeric value.

Excel Countif Syntax

The syntax for the Microsoft Excel COUNTIF function is:

Countif ( range, criteria )

Parameters or Arguments

range is the range of cells that you want to count based on the criteria.

criteria is used to determine which cells to count.

Example: Using Countif function based on criteria

Lets say you have a table showing Employees Names and whether they have been paid or not. So you want to use a function in excel in order to count the number of employees who have got their pay and also count the numbers who haven’t got their pay.

You can open Excel and enter the following data in Column A, B and C as shown in the figure below.

We will now use COUNTIF function in cell C12 to count the number of employees who have been paid. Lets start by entering the countif function as shown below

Enter the first parameter, which is our range from cell C2 to C9 (C2:C9)

Enter the second parameter, which is the criteria, in this case we want to count the number of Yes i.e. Y. So we will enter the criteria “Y”. Please note that in excel the criteria has to be inside inverted commas “ ” .

After you have entered the formula, press the Enter key and you will see it will show “5”, because it has counted the number of employees who have been paid.

Here's what valued users are saying about ProjectPro

ProjectPro is an awesome platform that helps me learn much hands-on industrial experience with a step-by-step walkthrough of projects. There are two primary paths to learn: Data Science and Big Data. In each learning path, there are many customized projects with all the details from the beginner to...

Jingwei Li

Graduate Research assistance at Stony Brook University

I am the Director of Data Analytics with over 10+ years of IT experience. I have a background in SQL, Python, and Big Data working with Accenture, IBM, and Infosys. I am looking to enhance my skills in Data Engineering/Science and hoping to find real-world projects fortunately, I came across...

Ed Godalle

Director Data Analytics at EY / EY Tech

Not sure what you are looking for?

View All Projects

Now in Cell C13 enter a similar formula to count the number of employees who did not get paid, that is to count the number of “N” (which stands for NO) in the table.

The formula will be  -->   =countif(C2:C9, “N”)

Once you press the Enter key you can see the values as shown in figure below.

Formulas used in this example:

About the Author

ProjectPro

ProjectPro is the only online platform designed to help professionals gain practical, hands-on experience in big data, data engineering, data science, and machine learning related technologies. Having over 270+ reusable project templates in data science and big data with step-by-step walkthroughs,

Meet The Author