fbpx

Learn how to use Countif function in Excel

Yodalearning>Tutorials>Learn how to use Countif function in Excel

Countif function will help you to know how many records of a certain type you have in your Excel spreadsheet.

The Excel Countif function returns the number of cells within a supplied range, that satisfy given criteria.

When to use Countif Function?

COUNTIF function can be used when you want to count the number of cells that meet specified criteria.

What does it return?

It returns a value(number) that represents the number of cells that meet specified criteria in the selected range.

Syntax

COUNTIF(range, criteria)

range – Defines one or several cells to count. You put the range in a formula like you usually do in Excel, e.g. A1:A20.

criteria – Defines the condition that tells the function which cells to count. It can be a number, text string, cell reference or expression. For instance, you can use the criteria like these: “10”, A2, “>=10”, “some text”.

Case Study: Let’s analyze result grade sheet of students in which we want to find out how many students have got GPA 4. We can find this using Countif Function.

Steps to Follow Using Countif Function:-

countif function in excel

Step 1: Create a table similar to the picture above.

countif function

Step 2: In the Cell E2 we will calculate the number of students who have got GPA 4. type this formula in cell E2:

=COUNTIF(B2:B9,4)

countif function

Step 3: Now press ENTER, you will get the value 3 which is a number of students who got GPA 4.

Tip 1:

countif function

In the Criteria portion of the formula, You can use also B3 to get the same result. Because cell B3 contains the value GPA 4. So, in that case formula would be:

=COUNTIF(B2:B9,B3)

Tip 2:

countif function in excel

Imagine that you want to find the number students who got GPA more than 3, then the formula would be:

=COUNTIF(B2:B9, “>3”)

So, we can also give mathematic condition in criteria.

Read More: Learn Text To Column in Excel with Example

Tip 3: If you want to find those students whose name contains Roy, then use this formula and see the magic:

=COUNTIF(A2:A9, “*Roy”)

We use * before Roy that means find those values who has Roy at the end. By giving * means whatever exists before Roy it will count that.

Tip 4: We can also Sum 2 Countif results in another cell. Think you want to sum these 2 Countif result of =COUNTIF(B2:B9,B3) and COUNTIF(B2:B9, “>3”). To do so, you can use this formula given below:

=COUNTIF(B2:B9,B3)+COUNTIF(B2:B9, “>3”)

Now You have successfully learned how to use CountIF Function in Excel.

Don’t forget to comment your take on the Countif Function.

1 Response
  1. sweetie

    Would like to know the difference between all count function Together. CountIF, CountA CountSum, Count and others.

Leave a Reply

Excel Dashboard
Limited Period Offer.

30-Day Money-Back Guarantee

Includes:


11 hours on-demand video
Full Lifetime Access
Success on mobile and TV
Certificate of Completion
Downloadable Case Studies
Lifetime Support