How to Use COUNTIF in Excel: Step-by-Step (+COUNTIFS)

The Excel COUNTIF function is a very smart mix of the COUNT and IF functions of Excel.

Using this function you can count cells that meet a specified condition. And as it’s you who’s going to define the condition – so you have the world open to you 🛫

And if you have more than one condition to define, you can use the COUNTIFS function. How to use both of these functions?

I will walk you through that with a lot of examples (literally a lot of them) in the guide below. So download our free sample workbook for this article here and dive in straight 📩

How to use the COUNTIF function

First question first, how do you use the basic COUNTIF function? The example below will teach you that.

The image hereunder has a list of different numbers.

I want to know how many 50s does this have? To find that:

1. Begin writing the COUNTIF function as follows;

=COUNTIF(

2. Write the first argument (range) of the COUNTIF function. This is the range that contains the cells to be counted.

In our example, this is Cell range A2:A8 🎯

= COUNTIF (A2:A8

3. Next, write the logical criteria argument (the IF condition) based on which the cells must be counted.

We only want to count those cells that contain 50. So our condition must be something equal to 50 (“=50”).

=COUNTIF(A2:A8, “=50”)

4. Hit Enter 🚀

Excel will now check each cell of the range A2:A8 against the specified criteria (is it equal to 50). And give results as below:

We have 3 cells in the given range that are equal to 50. That’s how the COUNTIF function counts cells 💪

COUNTIFS: COUNT with multiple criteria

COUNTIFS is an advanced version of the COUNT function. Or you may call it the COUNT function with multiple criteria.

Under the COUNTIFS function, you can specify up to 127 criteria. And Excel will evaluate each value from a criteria range against the criteria for it 🔎

Here is again a list of numbers. But this time, let’s find out the cells containing numbers that are greater than 50 but smaller than 50.

55 > Numbers > 50

So let’s go:

1. Write the COUNTIFS function as follows:

= COUNTIFS (

2. Write the first argument (criteria_range1) of the COUNTIF function. This is the range for the first criterion.

Our first criterion is greater than 50. Excel would check the numbers against this criterion, so our first criteria range is Cell range A2:A8 👀

= COUNTIFS (A2:A8

3. Next, write the first criteria. Excel will check the first criteria range against this criterion.

Our first criterion is greater than 50, so we are going to write it like that “>50”.

=COUNTIF(A2:A8, “>50”

4. Define the second criteria range.

Our second criterion is lesser than 55. So our criteria range is still the same.

= COUNTIFS (A2:A8, “>50”, A2:A8,

5. Define the second criterion (the criteria range 2 will be checked for it).

The second criterion in our example is smaller than 55 so, we are going to write it like that “<55”.

= COUNTIFS (A2:A8, “>50”, A2:A8, “<55”)

6. Hit Enter.

Excel will now check each cell reference of the range A2:A8 against both criteria (greater than 50 and smaller than 55).

And there are only 2 of them. Superb 💥

COUNTIF and COUNTIFS formula examples

The COUNTIF and COUNTIFS functions are way more versatile than that and we are going to see that through the examples below.

Count if greater than or less than a number

You can use the COUNTIF function with greater than (>) or less than (<) operators very simply.

We’ll do that just now. Peek into the data below that has a list of people with different heights.

Let’s quickly use the COUNTIF function to find the number of people who are taller than 5 feet 🕺

1. Write the COUNTIF function as follows;

= COUNTIF (B2:B8

As the first argument (range), we have referred to the cell range that contains heights.

2. Next, write the criteria based on which the cells must be counted.

We only want to count on those people who are taller than 5 feet. Yes, you guessed that right.

The criteria would be defined using the greater than (>) logical operator as “>5”.

=COUNTIF(B2:B8, “>5”)

3. Hit Enter.

We have 5 members whose height exceeds 5 feet.

And if we want to know the number of people who are shorter than 5.5 feet 🤔

4. Write the COUNTIF formula using the less than operator as below:

=COUNTIF(B2:B8, “<5.5”)

5. Hit Enter.

Only 4 of them! It is interesting to see how the COUNTIF works, isn’t it 🏆

Count if between two numbers

In the example above, we have seen people who are taller than 5 feet. And also those who are shorter than 5.5 feet.

Let’s now try to count the number of people (from the same above dataset) that are taller than 5 feet but shorter than 5.5 feet 🙈

Note that here we have two conditions to be checked simultaneously:

• Taller than 5 feet
• Shorter than 5.5 feet

To run the COUNT function with multiple conditions, we need to use the COUNTIFS function. So let’s go ahead with it.

1. Write the first criteria range and the first criteria for the COUNTIFS function.

= COUNTIFS (B2:B8, “<5.5”

As the first criteria range, we have referred to the cell range that has heights (B2:B8).

We have defined the first criteria for the first criteria range as less than 5.5 i.e.“<5.5”.

2. Write the second criteria range and the second criteria.

=COUNTIF(B2:B8, “<5.5”, B2:B8, “>5”)

The criteria range remains the same. Whereas the second criterion (taller than 5 feet) is defined as “>5”.

3. Hit Enter, and there you go.

The COUNTIFS function returns 2 – these must be Henry (5.3 inches tall) and William (5.4 inches tall) 🧐

Count if greater than a date

Who said COUNTIF and COUNTIFS were only meant to count numbers?

You can also use them with dates, see here 📅

The list below has meetings listed together with the dates when each of these is scheduled.

Let’s use the COUNTIF function to count the events that are scheduled before 30 June 2023.

1. Write the COUNTIF function as follows;

= COUNTIF (B2:B8

As the first argument (range), we have referred to the cell range that contains the dates.

2. Write the criteria as “<30-June-2023”.

As we want to count the dates before 30 June 2023, we will use the less than operator (<).

This way Excel will count the meetings that are scheduled before 30 June 2023 📌

=COUNTIF(B2:B8, “<30-June-2023”)

3. Hit Enter.

5 meetings there are! The COUNTIF function works for dates just like it does for numbers 😍

Pro Tip!

Can you use the COUNTIF function to count blank cells 🙋‍♀️

Well, certainly you can. For example, to count the blank cells A1 to A5, write the COUNTIF function below:

= COUNTIF (A1:A5, “”)

The answer to this will be 5 ✌ That’s because we have set the criteria blank. Excel will see if all the cells are blank and count them.

Similarly, you can use the COUNTIF function together with wildcard characters (asterisk matches, question mark matches, etc.) to perform partial matches.

Count if between two dates

We have seen the simple application of the COUNTIF function with dates. But what if you want to perform a count of dates that fall between two dates?

For the same meetings as above, let’s say this time we want to count the meetings scheduled before 30 June 2023 but after 28 February 2023.

These are two conditions 👇

• Before 30 June 2023 (<30-June-2023)
• After 28 February 2023 (>28-February-2023)

For multiple criteria, we need to use the COUNTIFS function. So let’s go.

1. Write the first criteria range and the first criteria for the COUNTIFS function.

= COUNTIFS (B2:B8, “<30-June-2023”

As the first criteria range, we have referred to the cell range that has dates (B2:B8).

And the first criteria will be before 30 June 2023 (in terms of logical operators “<30-June-2023”).

2. Write the second criteria range and the second criteria.

The criteria range remains the same (Cell B2:B8). However, the second criterion will be “>28-February-2023” 👩‍🏫

To count the dates that fall after 28 February 2023, we will use the greater than operator (>).

=COUNTIFS(B2:B8, “<30-June-2023”, B2:B8, “>28-February-2023”)

 

3. Hit Enter to see what Excel has got for us.

Excel says there are 3 meetings between 28th February and 30th June 2023 3️⃣

And guess what, that’s right.

• Meeting No. 1 on 31 May 2023;
• Meeting No. 2 on 25 March 2023; and
• Meeting No. 7 on 04 April 2023.

That’s it – Now what?

That’s all about the two bombastic and very commonly used logical functions of Excel – the COUNTIF and COUNTIFS functions 🥈

Both of them are lifesavers. You can use them to sort and evaluate your rows-long Excel data in seconds. And just like these two functions, the Excel function library is a whole pack of amazing functions.

To learn Excel functions, we suggest you start from the core Excel functions like the VLOOKUP, SUMIF, and IF functions. How?

Enroll in my 30-minute free email course to learn these (and many more) functions of Excel now.

Other resources

The COUNTIFS and COUNTIF functions will be the most useful when you can make optimal use of logical operators.

Read our blog on logical operators, and the guide on how to use them with IF functions.

Although very specific, using COUNTIF between two numbers is super practical!

Frequently asked questions