How to Use the COUNTIF Function in Excel

Today we’re gonna talk about how to use the COUNTIF function in Excel in a quick and easy way. This function comes handy when you need to count how many cells in a selected area meets specified criteria.

This means, for example, that we’re looking for how many people from this table are exactly eighteen years old. Thanks to this function, you can also get the count of people who are over sixty-five or whose name’s Maria – depends on what you need to find out.

So, let’s see how to do all this!

Would you rather watch this tutorial? Click the play button below!

To use the COUNTIF function, click into the cell you selected to display the result. Type in the equal sign and start typing COUNTIF.

Excel looks the function up. Select it with a click and now you’ll need to add two details.

How to Define COUNTIF Function in Excel

The first one is Range where we’re identifying the area through which Excel will search. The other detail is Criteria – you need to specify what bit of information Excel should look for.

We want to search the whole Age column, so we’ll select this area, then we’ll add a comma and enter the value for Criteria. In this case, we want to find out how many cells contain nothing else but the number 18.

Here, we’ve got two options. We can enter the fixed value 18, close the brackets and after we hit Enter, we get the number of cells which contain the value 18.

How to Tweak COUNTIF Function If You Need to Change the Criteria Regularly

Or, if we need to change Criteria regularly, we can tweak the function a little and have Excel extract the value for Criteria from a selected cell. Here we want to use the value stored in the cell G2, so instead of the fixed value 18, we’ll use the reference to the cell G2.

And here’s how it works – if we change the number in G2 to, let’s say, 25, we can immediately see that there are exactly two cells within the table which contain the number 25. Great news is that this principle applies to data tables of any size!

Now, let’s see what else the function can do.

Using COUNTIF Function to Comparing the Values

The COUNTIF function can also be used when the Criteria is based on comparing values – we can find out how many cells in the table contain values greater than or less than the number stated in the Criteria part of the formula.

Using COUNTIF Function with Fixed Values

For example, to find out how many people are over sixty-five here, type in the whole COUNTIF function just as you did in the previous step, select the area as before, but watch out! The criteria need to be specified in a slightly different way here. Let’s make the function work using the fixed value first. Introduce quotation marks and enter the greater-than sign within, followed by the number 65. Excel quickly returns the number 3, as there are three people over sixty-five in the table.

Using COUNTIF Function with Dynamic Values

And now let’s make the function dynamic, again. The cell G3 contains the criteria we specified. But when it comes to comparing values, a simple reference to a cell won’t be enough. Let’s try a little trick that should help. Type in the greater-than sign in quotation marks, then enter an ampersand followed by the reference to G3.

All’s done here, so press Enter and you’ll see the same result as a moment ago, when we used the fixed value. But if you change the value in G3 to 40, the function will promptly recalculate the result and Excel will show number 17, which means there are 17 occurrences of cells containing numbers greater than 40 in the Age column.

And before we wrap up, let’s have a look at how to use the COUNTIF function with text.

How to Use COUNTIF Function with Text

If we need to know how many people named Oscar there are in the table, we’ll start off just as before, but we’ll extend the selected area of the table to the columns containing names. And here’s another important thing.

To look for a piece of text, we need to include it in quotation marks. So, type in the quotation marks with the name Oscar within, close the brackets, press Enter and here we go! Now we know there are two Oscars in the data table.

Again, if you wish to use a cell reference rather than a fixed value, just include the letter of the column and the number of the row where the cell containing the value for Criteria is located, in our case it’s G4. If you type Maria into the cell, Excel will tell you right away that this name occurs in the table only once.

So, in today’s tutorial we saw a short demonstration on how to use the COUNTIF function in practice. We believe this function will make your working life easier.

And apart from COUNTIF, Excel offers loads of other useful functions. With EasyClick Academy, you can see tutorials to the most popular ones. Just check out the links in the list below!

Don’t miss out a great opportunity to learn:

• How to Calculate Difference Between Two Dates in Excel
• How to Sum a Column in Excel
• How to Use the VLOOKUP Function in Excel (Step by Step)
• How to Use IF Function in Excel (Step by Step)

If you found this tutorial helpful, give us a like and watch other video tutorials by EasyClick Academy. Learn how to use Excel in a quick and easy way!

Is this your first time on EasyClick? We’ll be more than happy to welcome you in our online community. Hit that Subscribe button and join the EasyClickers!

Thanks for watching and I’ll see you in the next tutorial!