How to use the Excel-functions COUNTIF and COUNTIFS (new)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

In this tutorial, I show you exactly how to use the 2 functions COUNTIF and COUNTIFS (which is new!).

In short, these functions count the number of cells within a range that contain a specific value.

A quick example:

Say we have a list of sales transactions for a team of sales people. We want to keep a count of sales by each member of the team.

The ‘COUNTIF’ function allows you to do that.

Let’s take the same example a little further…

Now we have a list that covers an entire year’s worth of data. This time we want to be able to get a count for a subset of that date range, like a month or quarter.

The ‘COUNTIFS’ function allows you to do that in an efficient manner.

Alright, I’ve set the scene for you. Let’s get into the details!

Kasper Langmann, Co-founder of Spreadsheeto

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

How ‘COUNTIF’ works

The ‘COUNTIF’ function is simple and straightforward.

Its syntax looks like this:

‘=COUNTIF(range, criteria)’

There are only two arguments for this function:

  • The first is the ‘range
  • The second is the ‘criteria

The ‘range’ is the range of data from which we want to count from.

The ‘criteria’ is the argument that indicates what we want to count. This can be an actual value like a number or a string. It can also be a cell reference or even a logical expression.

How ‘COUNTIFS’ works

The ‘COUNTIFS’ function is built on the same idea as the ‘COUNTIF’ function.

But, as the name implies, it allows us to set more than one range and criteria. This creates an enormous amount of flexibility.

The actual syntax for the two is shown for comparison below:

‘=COUNTIF(range, criteria)’

‘=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], [criteria_range3, criteria3],…)’

Note: for ‘COUNTIFS’ arguments, beyond the first criteria, range and criteria are optional.

Excel allows up to 127 criteria range/criteria pairs in the ‘COUNTIFS’ function.

This function allows you to drill down into data sets. With this, you can count based on a deeper level of requirements than what a simple ‘COUNTIF’ function will.

In a bit, we will contrast the two with some examples. But first, let’s look at ‘COUNTIF’ in action to get a stronger understanding of its fundamentals.

Kasper Langmann, Co-founder of Spreadsheeto

Using ‘COUNTIF’ with numbers

We will first look at the nuances of working with data sets involving numbers.

Let’s say we have a column of several different numbers. We want to count the number of instances of one of those numbers.

Kasper Langmann, Co-founder of Spreadsheeto

Our data range starts at A2 and ends with A15. Now, we want to count the number of times that the number ‘2’ is

Now, we want to count the number of times that the number ‘2’ is in that list. Our formula would then be

Our formula would then be:

 ‘=COUNTIF(A2:A15,2)’

This is pretty straightforward.

When you need a count of the data based on a comparison operator like “greater than some number”, you must enclose the expression in double quotes.

Like this:

  ‘=COUNTIF(A2:A5,”>5”)

Notice the entire expression ‘>5’ for our ‘criteria’ argument is enclosed in double quotes.

We can also use cell references as our ‘criteria’ argument!

Kasper Langmann, Co-founder of Spreadsheeto

If we have a cell that contains the number we want a count for, insert that cell reference like:

‘=COUNTIF(A2:A15,A2)’

We can also use comparison operators with cell references. To do so, we must do things a bit different than we did for a literal number.

In the case of a cell reference, we do not need to enclose the entire expression in double quotes. We just need to enclose the comparison operator in double quotes. Also, place an ampersand (&) between the comparison operator in double quotes and the cell reference.

Kasper Langmann, Co-founder of Spreadsheeto

This looks like:

‘=COUNTIF(A2:A15,”<>”&A2)’

The ampersand joins the value in our cell reference (A2) with the comparison operator ‘<>’.

Now our criteria argument says, ‘not equal to the value in cell ‘A2’. The formula itself will count all numbers in the range that are greater than or less than the number in ‘A2’.

The results of ‘COUNTIF’ functions can also be added together. So, if you need to find the count of many numbers in our range, you can do that. But, this gets quite messy and cumbersome beyond 2 or more terms.

Kasper Langmann, Co-founder of Spreadsheeto

This is where the ‘COUNTIFS’ function comes in handy. More on that in just a bit!

Data set
COUNTIF example

Using ‘COUNTIF’ with string data

You now know the basics of using ‘COUNTIF’ with numerical data.

Let’s switch gears a bit. Instead, let’s look at some of the differences when we are dealing with string data.

Kasper Langmann, Co-founder of Spreadsheeto

Not much is different than the concepts applied to numerical data – except for one important thing.

Strings always need to be enclosed in double quotes.

Like with numerical data, if we use a cell reference for our ‘criteria’ argument, the same rules apply.

Data set names
COUNTIF explained

Since we have covered the fundamentals, let’s look at some practical examples.

These bring a bit more meaning to the use of these two functions.

Scenario: Inventory management

Let’s say we have a list of products that we watch the inventory of. If it is below a total count of 20 we want to know about it – every week.

This allows us to spot patterns that may need investigating if our count trends upward. Using the ‘COUNTIF’ function will allow us to keep an eye on this metric. Best of all, it needs very little effort!

Kasper Langmann, Co-founder of Spreadsheeto

Simple enough right?

In the next figure, we have our inventory list.

inventory management example

Select the ‘Current Count’ column as our ‘range’ argument (B2:B11).

Then our ‘criteria’ argument will be the expression representing ‘less than 20’.

Our formula will be:

 ‘=COUNTIF(B2:B11,”<20”)’

Pretty simple…

Let’s say we want a quick calculation of what percent of the total products in our inventory are below a current count of 20.

Kasper Langmann, Co-founder of Spreadsheeto

The math is simple but let’s use ‘COUNTIF’ to perform this calculation for us.

We already have one-half of the calculation.

But to get the total number of line items, we will use the ‘COUNTIF’ function for the sake of illustration. We can get the count of all rows of data by using the wildcard ‘*’ for our ‘criteria’ argument.

See the figure below to see this in action…

Calculations with COUNTIF

Scenario: Numbers of awards won

Alright, let’s take another example.

Now we’re going to look at a data set containing the American League Cy Young Award winners since 1985.

Here, we’ll also look at techniques for finding counts within a subset of the data. This time we’re going to use both ‘COUNTIF’ and ‘COUNTIFS’ for the sake of comparison.

Kasper Langmann, Co-founder of Spreadsheeto

This is a case in which we can do what we need to with just the ‘COUNTIF’ function.

But in some of those cases, the ‘COUNTIFS’ function is much more effective in doing so.

Let’s get into it!

First, we perform a simple count. We’re going to count the number of times a given player has won the award since 1985.

Kasper Langmann, Co-founder of Spreadsheeto

Select the entire range ‘B2:B32’ and a player name as the criteria.

We will use a cell reference containing a drop-down list of all the players who have won the award in that date range.

Our formula for counting the number of times a given player has won the award over the entire range of years 1985-2015 is ‘=COUNTIF(B2:B32,G2)’.

‘G2’ is the cell containing our dropdown list of players.

This was easy. Let’s step it up a bit!

Now we want to count the number of times a player has won the award in the following subsets of the total date range: 1985-1989; 1990-1999; 2000-2009.

Kasper Langmann, Co-founder of Spreadsheeto

Again, this is possible using both the ‘COUNTIF’ as well as the ‘COUNTIFS’ function.

This time, we’re going to use both!

That makes it easy to see the contrast between the two. It also shows the simplicity with which the ‘COUNTIFS’ function performs a count based on more than one criterion.

To count the number of times a player has won the award from 1985-1989, change the ‘range’ argument to ‘B28:B32’ and nothing else.

Kasper Langmann, Co-founder of Spreadsheeto
Performing calculations with COUNTIF

It’s that simple.

To do this with the ‘COUNTIFS’ function we will end up with 2 criteria range/criteria pairs.

This may seem to contradict the idea that the ‘COUNTIFS’ accomplishes the same thing as ‘COUNTIF’.

But imagine if we had a larger data set that wasn’t sorted by year (or whatever the filtering criteria may be). While this specific example is simple laid out, it is intentionally done so to see how the results of our formulas are valid.

Kasper Langmann, Co-founder of Spreadsheeto

What we need to do is filter out only the dates before 1990 in our criteria range.

In contrast to how we did this with the ‘COUNTIF’ function, we don’t have to highlight just the range of dates from 1985-1989.

Instead, we can reuse the entire range (a named range that can be used over and over if we like) and set criteria of ‘<1990’ or ‘<=1989’.

Both expressions mean the same thing when dealing with a once per year event. The first expression says, ‘less than 1990’ while the second says ‘less than or equal to 1989’.

Kasper Langmann, Co-founder of Spreadsheeto

Either expression will select dates up to, but not including 1990.

Named Ranges (quick tip)

When working with the same range of data on a repetitive basis, Excel has an awesome feature you should use.

It’s called “Named Ranges“.

A named range enables you to select a range of data and name it for future reference.

In our current scenario, let’s create a named range for our year data.

Kasper Langmann, Co-founder of Spreadsheeto

First, let’s select the range A2:A32.

With the entire range highlighted, go to the ‘Formulas’ tab.

Select ‘Name Manager’ in the ‘Defined Names’ section.

Name Manager

Once the ‘Name Manager’ window appears, click on ‘New…’.

Then choose a name for the range and fill it in on the ‘Name’ entry of the form.

Note that the syntax of a range name does not allow spaces.

So, when using a name that is more than one word, either remove spaces between words – or replace them with an underscore.

Name Manager dialog box

We will use ‘Award_Year’ in this case. We will not change anything in the other fields.

Now click ‘OK’ and we can use ‘Award_Year’ instead of the actual range ‘A2:A32’.

Name giving

Now we have established our first criteria range/criteria pair.

Let’s set our second criteria range argument. This is the range of player names in column B. Then our criteria argument itself will be the cell reference, ‘G12’.

Kasper Langmann, Co-founder of Spreadsheeto

This is where our drop-down list of players in the range is.

As we change our selection, the result of the formula will change accordingly.

Notice in the following figure how we adapt our ‘COUNTIFS’ formulas to count within different data ranges.

COUNTIFS formula

Using wildcards

We could also count the number of times players won the award who played for a team name ending in ‘n’ (team city).

You do this by using the ‘*’ wildcard.

The ‘*’ matches any sequence of characters which makes it the perfect choice for this task.

If we wanted to set our wildcard for any single character, we would use the ‘?’ character.

Kasper Langmann, Co-founder of Spreadsheeto

We select the range of team cities in column C and create a named range called ‘Award_Team’.

Our ‘COUNTIF’ will look like ‘=COUNTIF(Award_Team,”*n”)’.

We can also do this for the date ranges we used in the previous example for the sake of comparison.

Let’s review the results and compare it to the actual data. Here we’ll see that there are 5 Boston players who have won the award over the entire date range – and one player from Houston.

Kasper Langmann, Co-founder of Spreadsheeto

See the following figure for the results and formulas.

COUNTIF and COUNTIFS calculations

Conclusion

There are many ways to count the occurrence of some data within a range.

However, the functions ‘COUNTIF’ and ‘COUNTIFS’ provide a logical procedure to make thing easy. This is especially true when using the ‘COUNTIFS’ function.

Kasper Langmann, Co-founder of Spreadsheeto

It allows us to drill down into the data by using multiple criteria range/criteria pairs.

We have also seen how the use of named ranges can simplify things. Named ranges create a higher level of efficiency when multiple range/criteria pairs become necessary in the use of the ‘COUNTIFS’ function.

That’s it for this tutorial on COUNTIF and COUNTIFS.

Did you like it?

Great. Then I’ve got good news for you…

2019-10-16T11:54:02+00:00