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 the range and criteria in the COUNTIF Excel function as follows:

1	=COUNTIF(A2:A1000,"Microsoft")

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 cell range “A2:A12”.  The result is displayed in the B14 cell after using the COUNTIF function.

This is how COUNTIF is used:

1	=COUNTIF(A2:A12,"London")

The resulting image:

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:

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 column B and used the 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:

1	=COUNTIF(A2:B12,"London")+COUNTIF(A2:B12,A2)

The count result:

You see, the occurrence of London is ten times while New York City is 4 times, and the total count is 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 a value greater than 1. See the result and formula:

Formula:

1	=COUNTIFS(A2:A12,"London",B2:B12,">1")

The count:

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 mentioned earlier, you may also use the expression in the 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:

1	=COUNTIF(B2:B13,">1000")

Count result:

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:

1	=COUNTIF (B2:B13,"<>"&B2)

Did you notice the ampersand (&) sign? This is used to merge the comparison operator with the 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:

1	=COUNTIF(B2:B13,">=1200")+COUNTIF(B2:B13,"<1800")

The count result:

You see, the result is 19. First, the formula got 9 based on the 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:

1	=COUNTIFS(B2:B13,">=1200", B2:B13,"<1800")

The outcome:

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 fruit and vegetable names. However, the 6,8, and 10 cells are kept blank. The following formula is applied to get the row count with *:

1	=COUNTIF(A2:A11,"*")

The result:

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 ‘?’:

1	=COUNTIF(A2:A11,"????to")

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

An example of COUNTIF with dates and range names

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

First, a 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 who joined after 2008. The third report is the number of employees who joined before or in 2008.

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

Now, I used the following three formulas:

• =COUNT(empData) – to get the total number of employees
• =COUNTIF(empData, “>” & “2008-12-31”) – to get the 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:

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 who joined between 2008 to 2012.

The formula:

1	=COUNTIFS(empData,">="&"2008-01-01",empData,"<="&"2012-12-31")

The output (notice the last one)