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 a given criteria.

When to use Countif Function?

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

What does it return?

It returns a value(number) that represents the number of cells that meet a 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 functionStep 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 excelImagine 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.

Leave a Reply

Advanced Excel Course

Your courses were useful for me. Thank you again for your quick support and also for your availability.

- Madalina Elena Gheorghe

Free Excel Course
with 210+ Tricks
(UPDATED 2017)