How to use COUNTIF function in Excel – 8 examples

Our guide explains how you can use COUNTIF in Excel properly.

An Excel icon with the letter 'x' centered in the foreground, with the words "COUNTIF Function Guide" and additional graphic elements in the background.

PC Guide is reader-supported. When you buy through links on our site, we may earn an affiliate commission. Prices subject to change. Read More

Last Updated on

Wondering how to use COUNTIF in Excel? We’ve got you covered.

Excel has many functions you can use in different scenarios. These functions can prove to be extremely useful in several situations, especially when dealing with a large data set. One of the functions is COUNTIF, which can help speed up the work. However, using it can seem confusing at first, especially if you’re new to the software.

In this guide, we’ll explain how to use the COUNTIF function in Excel so you can finish your work quickly.

How to use COUNTIF in Excel

Scenario on hand: We have a dataset of student’s marks. 

What we want to accomplish: Explore how to use the COUNTIF function in Excel in the following ways:

  1. Simple mathematical criteria
  2. Simple mathematical criteria with cell reference
  3. Multiple mathematical criteria
  4. Exact match
  5. Partial match
  6. OR criteria
  7. Count blanks
  8. Count duplicates

Simple mathematical criteria

The COUNTIF function is quite simple because it only requires a range and criteria. In our dataset, we have 9 student names and the marks they obtained throughout a course. We want to count the number of students who qualify a grade criteria. 

To do this, we define the criteria first:

The image shows an open Microsoft Excel spreadsheet with a dataset titled "Dataset for using the COUNTIF Function in Excel." It contains a table with student names, their test scores for five assessments, total marks

The next step is entering the formula. Here’s the syntax of the COUNTIF function in Excel:

=COUNTIF(range, criteria)

For our case, here’s what the formula looks like:

=COUNTIF($I$6:$I$14,K6)

Note that we add $ to the range to make the formula dynamic. We input this formula in the first cell, then drag it using the small square at the bottom called the ‘Fill Handle Tool.’

Here are the results:

A screenshot of an excel spreadsheet showing a dataset for using the COUNTIF Function In Excel, with student names, test scores, total marks, and a percentage-based grading criteria section.

In this example, we have used the ‘lesser than’ and ‘greater than’ criteria. However, we have summarized all the simple mathematical criteria for you in a tabular form so you can refer to it while using the COUNTIF function in Excel:

CriteriaFormula ExampleDescription
Count if equal to=COUNTIF(B3:B16,”=90%”)Count cells where the value is equal to 90%. 
Count if less than=COUNTIF(B3:B16,”<90%”)Count cells where the value is less than 90%
Count if greater than=COUNTIF(B3:B16,”>90%”)Count cells where the value is greater than 90%
Count if less than or equal to=COUNTIF(B3:B16,”<=90%”)Count cells where the value is less than or equal to 90%.
Count if greater than or equal to=COUNTIF(B3:B16,”>=90%”)Count cells where the value is greater than or equal to 90%.
Count if not equal to=COUNTIF(A2:A10,”<>90%”)Count cells where the value is not equal to 90%.

Simple mathematical criteria with cell reference

In the above method, we added the > and < signs in the cell reference. We repeat the same example, but without the > and < signs in the cell reference:

A screenshot of a Microsoft Excel spreadsheet showcasing a dataset used for the COUNTIF function in Excel, with a column for student names, tests, midterm, final, total marks, and final percentage, along

To calculate the number of students below the defined criteria, here’s the formula we use:

=COUNTIF({range},”>”&{cell reference})

For our dataset, the formula looks like this:

=COUNTIF($I$6:$I$14, “>”&K6)

Here’s the result:

A screenshot of a Microsoft Excel spreadsheet with a dataset for student test scores, showing a cell formula for the COUNTIF Function In Excel being used to count the number of students meeting a specified criteria, highlighted

Expanding the formula using the Fill Handle Tool, we get:

Spreadsheet displaying a dataset for using the COUNTIF Function In Excel, where student names, test scores, a final project, total marks, and percentage are listed, with a column highlighting the countif

Multiple mathematical criteria

In the first method, we found how to use the COUNTIF function to count the number of entries with simple mathematical criteria. The use of this function shows that all 9 students have secured above 60% and 70% marks. 

In this method, we introduce grades to the criteria like this:

A screenshot of an Excel spreadsheet with a student dataset featuring names, test scores, and a total marks column, alongside a grading criteria table. The COUNTIF Function in Excel usage is highlighted.

To calculate the grade each student has secured, we have to use the following COUNTIF formula:

=COUNTIF(range,”criteria 1″)-COUNTIF(range,”criteria 2″)

=COUNTIF($I$6:$I$14,L7) – COUNTIF($I$6:$I$14,L6)

Here are the results:

A screenshot of a Microsoft Excel spreadsheet using the COUNTIF Function In Excel, featuring a dataset with student names, test scores, a total mark column, and a small table showing grade criteria with corresponding student

Exact match

In this method, we want to see how many times students secured 10/10 marks on their tests. 

Here’s the formula we use for this:

=COUNTIF(C6:F14,”10″)

Upon pressing enter, we see that on 8 instances, students have secured 10/10 marks on their tests throughout the course:

A screenshot of a Microsoft Excel spreadsheet highlighting the use of the COUNTIF function, with a dataset for student test scores and totals alongside two calculated values showing the count of marks greater than or equal to.

Partial match

In our dataset, we want to count student names that have “son” in them. To do this, we modify the formula a bit with wildcard characters like this:

=COUNTIF(range, “*{phrase}*”)

Here’s what it looks like for our dataset:

A screenshot of an excel spreadsheet with data for a dataset utilizing the COUNTIF Function In Excel, showing a table with student names, their test scores, total marks, percentage, and a column

We get a count of three names with the letters “son” in them. 

OR criteria

If you want to count cells that have either one value or the other, the OR criteria is the way to go. 

The OR criteria is nothing but a + in between two COUNTIF functions. 

In our dataset, we want to find names of students partially matching “son” or “ez.” Here’s the formula we use for it and the result:

=COUNTIF(B6:B14, “*son*”) + COUNTIF(B6:B14, “*ez*”)

A screenshot of a spreadsheet displaying a dataset for using the COUNTIF Function in Excel, with an example formula highlighted in the formula bar and a numerical result shown in a cell within the spreadsheet.

Count blanks

In Excel, you can even use the COUNTIF function to count the blanks in your data set. For this example, we have modified the data to show two blank cells. We then input the following formula to count the blank and non-blank cells:

  1. Blank cells (all data types): COUNTIF({range},””)
  2. Blank cells (text): COUNTIF(range,”<>”&”*”)
  3. Non-blank cells: COUNTIF({range},”<>”&””)

Here’s the output when we use formulas 1 and 3 to calculate blank and non-blank cells:

A screenshot of an Excel spreadsheet demonstrating the use of the COUNTIF Function in Excel, with columns for student names, various test scores, total marks, and a percentage. One cell is highlighted in red

Count Duplicates

You can even use the COUNTIF function to calculate duplicates in your dataset. A simple loop formula using COUNTIF is required for this:

=COUNTIF({table range}, {value})>1

In our dataset, we want to see whether the students scored the same marks they did in their first assessment. 

Here’s the formula we use:

=COUNTIF(C6:G6,C6)>1

The image shows a screenshot of a Microsoft Excel spreadsheet featuring a dataset used to demonstrate the COUNTIF Function in Excel. The spreadsheet includes a table with columns for student names, scores for test 1,

Using the Fill Handle Tool, we expand the formula and see that four students have secured the same marks in two or all three of their tests:

A screenshot of an Excel spreadsheet displaying a dataset of student names and their test scores with columns for Test 1, Test 2, Midterm, Final Project, and Total Marks. The spreadsheet also

Wrapping up

And that’s how you can use the COUNTIF function in Excel. Clearly, this function is handy because it saves you a lot of time going through your results and counting them individually. Instead, let Excel do all the hard work for you by using this function following the steps above.

Learn more about Excel and how it works through these guides: