How To Use COUNTIF Function In Excel

Find the cells you need with this guide on using COUNTIF properly

How to use COUNTIF function in Excel

Last Updated on

The COUNTIF function is a great way to speed up your spreadsheet work, but not everyone knows how to properly use it. You’ll want to use it to find cells that meet a specific condition. Check out our guide below on how to use the COUNTIF function in Microsoft Excel – then can use it with confidence.

1

How to use COUNTIF

How to use COUNTIF in Microsoft Excel

Step

1

Open Up Your Excel Spreadsheet

Open up your Microsoft spreadsheet and make sure all the columns and rows in your table are filled with the appropriate data.

Step

2

Select An Empty Cell

Then, select an empty cell on your spreadsheet where you want your new data to go.

In that empty cell, type out the formula: =COUNTIF(

Step

3

Input the range

After the first bracket, you will need to input the range of cells you want the function to work for.

You can either select the first cell and drag your cursor to select your range of cells, or you can type it in manually in the original empty cell with your COUNTIF formula.

For example, the cells between A1 and A5 will be written as: =COUNTIF(A1:A5

Step

4

Input criteria

Next, you need to type a comma (,) and then input the criteria of the function inside a set of quotation marks.

This is what you will be searching for in your selected cell range – so type in the keyword which is your criteria, close the quotation marks and brackets, and then press enter.

Your final COUNTIF formula should look like this: =COUNTIF(A1:A5, “red”)

Step

5

Test

When you press the enter key on your keyboard, the COUNTIF formula in the cell will then change to the correct answer.

So, in our example COUNTIF formula, the function will count how many times the word ‘red’ appears within the selected cell range and give you the answer as a numerical value e.g. 2.

This means that the word ‘red’ appeared twice in the selected cell range.

Step

6

Swap Out The Keyword

Alternatively, you can swap out the keyword for a cell reference so the function will search the selected cell range for whatever is written in that cell.

This means that you can type out a COUNTIF formula that looks like this instead: =COUNTIF(A1:A5, “B1”)

This will search the selected cell range for whatever criteria is in the cell B1 and give you the answer in your selected original cell.

2

Using COUNTIF For Broader Searches

You are not just limited to using COUNTIF to search for a single term or keyword.

Instead, you can make broader searches using the COUNTIF function.

For example, you may want to count how many results you have in a selected cell range that are over the number 10.

Step

1

Single COUNTIF

Instead of searching separate COUNTIF functions for numbers like 11, 12, and above, you can just use one COUNTIF formula to find the result you need.

Step

2

Similar to before

To do this, follow the same steps above but change the COUNTIF formula. You will want to write: =COUNTIF(A1:A5,”>10”)

This formula will count how many results you have in that selected range that are above the number 10.

Step

3

Count results

To count how many results are below a certain value, swap out the greater than symbol (>) for the lesser than symbol (<).

To count how many results are between a certain number, use both <> together and then add “&B1” to show the value you want to search for.

Conclusion

And that’s how you can use the COUNTIF function in Microsoft Excel! Clearly, this function is a handy one to know 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 in the steps above.

You might like this

Adding a trendline to a chart can make it easier for readers to interpret the data.
Find out how to count cells in Excel with our guide - and several useful methods
Are you trying to make your Microsoft Excel sheet easier to read for others?
Are you trying to make your spreadsheet more readable for Excel’s formulas? Here's how

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on pinterest
Managing Editor
at
PC guide
Kevin has been a technophile since he first owned a Commodore 64. Then an Amiga….progressing to Gameboy, self-built systems, consoles, and a 15-year career in and around computing and technology. Kevin is an all-around tech and gadget enthusiast. He was previously found at such places as Micro Mart, Custom PC, Bit-tech, and PC PRO, then Which? Computing, Den of Geek, and Daily Telegraph. Also WIRED, Hardware Heaven, and KitGuru.

Independent, transparent, rigorous and authentic, our reviews are the most thorough and honest in PC gaming. Learn about our review process.

Leave a Comment