Your Guide to the Excel COUNTIF Function

Holly Hunt
Holly Hunt  |  December 27, 2018

If you're trying to widen your Excel knowledge, you've come to the right place.

That large spreadsheet you spent hours laboring over might look so overwhelming, your boss has a hard time gleaning anything from it. Thankfully, Microsoft Excel makes it easy to call out the important information you want to summarize. How? Through functions.

Functions are pre-set Excel formulas designed to make your life easier.

In this article, we will cover the Excel COUNTIF function, which is an excellent way to call-out the important takeaways on your spreadsheet.

How to use Excel COUNTIF function

Let's go over what exactly the COUNTIF function is, and then how you can use it to your advantage. 

What is Excel COUNTIF?

COUNTIF is a function in Excel that you can use to count cells that meet a certain criteria. This is a great way to point out the larger takeaways of your data without manually going through and counting the specific cells yourself. 

How to use Excel COUNTIF

The COUNTIF function requires you to specify a range and a criteria. The equation looks like this: 

=COUNTIF:(range, criteria)

The range is the cells you wish to analyze. For example, cells B12 through B150 would be a range.

The criteria is what you want to count. For example, any cell that contains the value 100. 

You would write the function like this: 

=COUNTIF:(B12:B150,100)

Excel COUNTIF example

Want to see COUNTIF in action? Let's go through it step-by-step.

I'm going to use a personal example from a spreadsheet I made just a day ago. It's my training regimen for the Chicago Marathon in October. (Yes, now you know I am a masochist). 

Let's say I wanted to know how many of my long-run days are over 8 miles so I can mentally prepare for that torture. 

1. Type in =COUNTIF in the formula bar at the top. As you type, Excel will offer function suggestions. Click on COUNTIF.

excel-countif

2. Type in the range. Again, Excel will help you along by showing you what the function requires, as well as including the parenthesis. 

countif-excel

For this example, my long runs are always on Sunday, so I want to include all the values in the H column. When you type it in, Excel will highlight the range.

countif-function-in-excel

3. Type in your criteria. In this example, I want every value greater than 8. So I typed in quotes, the greater sign, and 8. You'll notice Excel highlights the cell where it is going to place the results. You can always move this around later.

how-to-use-countif-in-excel

4. Hit 'Enter.' Your result will appear in the highlighted cell to the side. Now I know I have 12 very long runs to look forward to in my future.

countif

Why use Excel COUNTIF

There are many different scenarios where you may want to use Excel COUNTIF. I'll list a few below to help you start thinking strategically about how to incorporate this function into your work. 

  • If you've mastered how to make a budget, you can give a report on how many items are in the negative.
  • You can highlight duplicate values that may have been made in error.
  • You can count cells that contain certain text such as "REQUIRED."

You're ready to use COUNTIF!

This is just one of many functions you can use to make your Microsoft Excel experience enjoyable! Check back for more guides on Excel functions. In the meantime...

Need a refresher on other ways to manipulate Excel data? Check out:

Holly Hunt
Author

Holly Hunt

An avid reader and writer, Holly graduated from the University of Missouri with a dual major in Journalism and English. Prior to joining G2 Crowd, she lived in Madison, WI, ate a horrifying amount of cheese curds, and then found her way to Chicago for a content writing gig. In her free time, Holly is eating ice cream and planning her next trip.