How to Use the COUNTIF Function in Excel

This article explains how to use the COUNTIF function in Excel. The instructions in this article apply to Excel in Microsoft 365, Excel 2019, and Excel 2016.

How to Use The COUNTIF Function in Excel

The COUNTIF function can be input manually or using Excel's Formulas menu. In either case, the eventual formula will look something like:

=COUNTIF(D4:D10,"Yes")

In this COUNTIF example, the function would search through cells D4 through D10 looking for the word 'Yes.' It would then output a number of how many times it finds it in the cell you input the formula in.

You can write this in manually if you prefer, but an easier method is to use Excel's Function menu.

1. Open the Excel document that you want to use the COUNTIF function in and make sure all of the data you want to use is present and correct.

2. Select a cell where you want the COUNTIF function results to appear, then input the formula into that cell. Alternatively, use the menu system. Select the Formulas tab, then from the Function Library section of the Ribbon, use the More Functions > Statistical to find the COUNTIF function.

   

3. In the Function Arguments window that appears, either type in the Range (the beginning and end, separated by a colon) or click/tap and drag through the cells you want to be considered in the calculation. In our COUNTIF example, that's cell D4 through D10, so it's input as D4:D10.

   

   You can use a question mark to match with any one character and an asterisk matches any sequence of characters.

4. Type or select the Criteria that you want COUNTIF to count. In our example, we want to know how many Yes results are in the D column, so we input Yes.

   These steps can be repeated for the No responses, ultimately learning there were two No results in the list of responses. The COUNTIF function can be used on a near infinite amount of data, and the larger that dataset is, the more useful COUNTIF can be.

5. Select OK. If you input everything correctly, you should see the result appear in the cell you performed the COUNTIF function in. In this example, the result of 5 appeared.

   If you want to look for results in multiple ranges simultaneously, you can do so, but you'll need to use the COUNTIFS function instead.

What is the COUNTIF Function?

Excel is an excellent tool when it's manually controlled, but it's even better when you can automate parts of it. That's where functions come in. From adding numbers together with SUM to removing non-printable characters with CLEAN. COUNTIF works in much the same way, but its job is to count the number of cells that match a certain criteria. It can be used to count cells that have certain figures in them, certain dates, text, special characters, or anything else you want to differentiate them with.

It takes inputs and spits out a total number depending on your chosen criteria.