How To Use COUNTIF Function In Excel

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

Kevin Pocock

Last Updated on July 19, 2022

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.

How to use COUNTIF

How to use COUNTIF in Microsoft Excel

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

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(

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

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”)

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.

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.

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.

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.

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.

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.