The COUNTIF function in Excel

You want to count the number of times word “Microsoft” appears from the cell A2 to A1000. How can you do that? One way is to go through each cell one by one and keep adding as you find a match OR

Use the Microsoft Excel COUNTIF function (a statistical function) that is used to count the number of cells for the given criterion.

Simply provide range and criteria in the COUNTIF Excel function as follows:

Just select the cell where you want to display the result and there you go! So, In the COUNTIF function, you need to specify

  • Where to search?
  • What to search?

In the next section, I will show you a variety of examples of using the COUNTIF function in excel with images, so keep reading.

Syntax of COUNTIF function

The syntax of using the COUNTIF function is:

COUNTIF(range, criteria)

Where:

  • The range is required. The range specifies where to search in excel sheet (numbers, arrays, a named range or references that contain numbers can be given as range).
  • The criterion is the condition that you want to test. It may contain text e.g. “London”, cells reference e.g. C34, numbers e.g. 10, 20, 30 or comparison e.g. “>15”, “<100” and so on. This is also required.
  • The COUNTIF can take only one criterion. If you want to specify more than one, use the COUNTIFS function.
  • The criteria are case insensitive.

An example of using Excel COUNTIF with text

The first example demonstrates using the COUNTIF function for counting the occurrence of word “London” in cells range “A2:A12”.  The result is displayed in the B14 cell after using the COUNTIF function.

This is how COUNTIF is used:

The resulting image:

Excel COUNTIF

The example of COUNTIF with cell text

For this example, rather than providing the text in COUNTIF function, the cell’s text is used as the criterion. For that, you may place the cell after the range as follows:

=COUNTIF(A10:A20,A8)

In that case, the A8 text is used to search and count in the given range (A10 to A20 in that case). See the following example:

The formula:

=COUNTIF(A2:A12,A8)

The resulting image:

Excel COUNTIF cell

You see, the A8 cell contains “New York City” and the count result is = 2.

Specifying multiple criteria in COUNTIF function

What if you require the count of two cities in our example sheet i.e. “London” and “New York City”? You may use COUNTIF twice or more for accomplishing this. For example:

=COUNTIF(A10:B10,A5)+COUNTIF(A12:B24,B7)

So, one criterion can be specified per expression and plus sign is used. Similarly, you may add more expressions with separate criteria.

See the following example where I have added more cities in the column B and used expression twice. The result should be the count of London and New York City. See the formula and output:

The formula for using COUNTIF in Excel twice:

The count result:

COUNTIF multiple

You see, the occurrence of London is ten times while New York City is 4 times and total count displayed as 14.

Note: Alternatively, you could also use COUNTIFS function for multiple criteria. See the example below.

The example of using multiple criteria by COUNTIFS

In the following example, two criteria are specified by using the COUNTIFS function of excel. The occurrence of London in column A with corresponding numeric value in column B. We will get the count of London and value greater than 1. See the result and formula:

Formula:

The count:

COUNTIFS Excel

Although the London occurrence is five times, however, in column B, one corresponding numeric value is 1, so the count is 4.

Using the number expression – COUNTIF greater than example

As mention earlier, you may also use the expression in Excel COUNTIF function. For example:

  • “>” greater than
  • “<” less than
  • “<=” less than or equal to
  • “>=” greater than or equal to
  • “<>” Not equal to

In the next example, we will count how many Months have sales over 1000. For that, we have a Month column (A) and the other represents the number of sales (B).

Formula applied:

Count result:

COUNTIF greater than

The example of not equal to <> operator

We will use not equal to operator with cell rather than a direct value. The requirement is to return the count of sales in a Month not equal to 1500. Have a look how it is done.

Formula:

COUNTIF not equal to

Did you notice the ampersand (&) sign? This is used to merge the comparison operator with cell value. It makes value of B2 for reading as =COUNTIFS(B2:B13,”<>1500″).

If we had used the =COUNTIFS(B2:B13,”<>”B2) (without ‘&’) then the result would have been 12.

Tip: So be careful as you intend to compare numbers and taking the value of cells.

Using multiple expressions example

You may also use multiple expressions for defining two or more conditions.  For example, return the Months count (in our example sheet), with sales greater than or equal to 1200 and less than 1800.

This is how it can be translated into Excel’s COUNT IF function:

Formula:

The count result:

COUNTIF mutiple expressions

You see, the result is 19. First, the formula got 9 based on first COUNTIF and 10 for the second COUNTIF function. The + is used to sum up the two and so result is 19. If we used ‘-‘ minus, the result would have been -1.

Not the output you expected by giving two expressions? Well, might be you were expecting Month count between 1200 and 1900 rather than the sum of two? If that was the case, see next example.

Using COUNTIFS for multiple expressions

This should answer your question. The two expressions are given in the COUNTIFS function to get the count of Months with sales between 1200 (greater than or equal) and less than 1800. Now see the formula and output:

The outcome:

COUNTIFS mutiple expressions

So, the count is 7 i.e. in between 1200 (inclusive) and 1800 (exclusive).

Using (*) wildcard example in COUNTIF with blank cells

The (*) wildcard character is used to count cells with any text. The example below shows how to use this. Along with using * as a wildcard, another thing to learn in this example is that a few cells are kept blank to show how COUNTIF treats blank cells.

I have filled the cells from A2 to A11 with fruits and vegetable names. However, the 6,8 and 10 cells are kept blank. The following formula is applied to get the row count with *:

The result:

COUNTIFS wildcard asterisk

You see, * counted all rows with any text while the blank cells are not counted.

The example of ‘?’ wildcard

The question mark (?) is also a wildcard that can be used in COUNTIF function. It is used to match the individual character.

This example gets the count of cells where the text has 6 characters and ends at “to”. See how to use the ‘?’:

COUNTIFS winlcard question mark

You see, it returned the count as 2 for Tomato and Potato.

An example of COUNTIF with dates and range names

In all above examples, I used cell range like A2:10 etc. For this example, two more things are done for using the COUNTIF function.

First, range name is used. Secondly, the counting is based on dates. For that, employee’s sheet is created that contains Employee Names and their Joining date.

We will get three reports based on that small data. The total number of employees by using the count function. The number of employees joined after 2008. The third report is the number of employees joined before or on 2008.

The cells containing data of employees is named empData as shown in the graphic below:

COUNTIFS range_name

Now, I used the following three formulas:

  • =COUNT(empData) – to get total number of employees
  • =COUNTIF(empData, “>” & “2008-12-31”) – to get number of employees joined after 2008
  • =COUNTIF(empData, “<=” & “2008-12-31”) – employees joined on or before 2008

The results are displayed in the cells B9, B10, and B11 cells as shown below:

COUNTIFS date

Getting employees data between two dates example

Just like in one of the above examples, we got the count of the number of sales between two numbers by using COUNTIFS function. Similarly, you may count between two dates by using COUNTIFS function. Using the same datasheet as in above example, I will apply the formula to get the count of employees joined between 2008 to 2012.

The formula:

The output (notice the last one)

COUNTIFS date range