Excel COUNTIF Function (10 Examples)

Excel COUNTIF Function

The Excel COUNTIF function is used to count the number of cells that meet a certain criterion. The Excel COUNTIF function supports a single condition. Users may, for example, count the number of times a particular city appears in a customer list.

In this section:

---

1. Syntax of the Function COUNTIF.
2. Important Notes on COUNTIF.
3. Count cells equal to a text or name.
4. Count cells greater than a certain number.
5. Count if cells is not equal to certain text or name.
6. Count if cells equal to a certain number.
7. Count if cells are blanks.
8. Count if cells are not blank.
9. Count the number of cells with a value greater than or equal to a certain number.
10. Use COUNTIF for different Worksheets in the same workbook.
11. Use COUNTIF for different Workbooks.

---

1. Syntax:

=COUNTIF(Range, Criteria)

where,

Range: The range of cells where you want to look.

Criteria: The range criteria for which you want to count the cells.

2. Important Notes on the use of COUNTIF:

1. The COUNTIF function counts the number of cells in a range that satisfy a single condition.
2. COUNTIF function CANNOT count cells of an array (i.e., you cannot use 2*3, 3*1 cell selection)
3. The COUNTIF criteria are NOT case sensitive. For example, the string “ORANGE” and “orange” are same.
4. Users can use the Wildcard characters in the COUNTIF criteria.
5. While counting text values, be sure that data set does not contain leading spaces, trailing spaces, inconsistent use of straight and curly quotation marks, or nonprinting characters. If you use any of these, the COUNTIF will return unexpected value.
6. COUNTIF supports named ranges in the formula, and the ranges can be in the same Worksheet, another Worksheet from the same Workbook, or from a separate Workbook.
7. While using references from another Workbooks, those Workbooks MUST be opened.
8. The criteria can be logical operators (e.g., =, >, <, <=, >=, <>).
9. You MUST enclose the criteria argument within quotes (” “).
10. Do not use string references longer than 255 characters.

3. Example 1: Count cells equal to a text String or a Name (Fruits=”Apple”):

In our example, we want to count the presence of “Apple” in column B. The formula is =COUNTIF(B5:B16, “Apple”), which returns the value of 2.

4. Count cells greater than a certain number (Unit Sold>600):

In our example, we want to count the cells where unit sold is more than 600. The formula is =COUNTIF(D5:D16, “>600”), which returns the value of 2.

5. Count if cells is not equal to certain text or name (City is NOT “Miami”):

In our example, we want to count the cells where city is NOT “Miami“. The formula is =COUNTIF(City, “<>Miami”), which returns the value of 7.

6. Count if cells equal to a certain number (Unit Sold=600):

In our example, we want to count the cells where unit sold is equal to 600. The formula is =COUNTIF(units, “=600”), which returns the value of 3.

7. Count if cells blank (Blank cells):

In our example, we want to count whether there is any blank cells in the unit sold column. The formula is =COUNTIF(units, ” “), which returns the value of 0 because we can see that there is no blank cell.

8. Count if cells are NOT blank:

In our example, we want to count how many cells NOT blank in unit sold column. The formula is =COUNTIF(units, “<>”), which returns the value of 12 because we can see that all the cells have data.

9. Count the number of cells with a value greater than or equal to a certain number. (“>=600”):

In our example, we want to count how many cells have sales equal to or more than 600 units. The formula is =COUNTIF(units, “>=600”), which returns the value of 5 because we can see that 5 cells have unit sold more than or equal to 600.

10. Use different worksheet reference from the same workbook:

In our example, we are working on the Example-7 worksheet and want to use the reference to the worksheet “Example-4“. The formula is same except the reference to other worksheet. The formula is =COUNTIF(‘Example-4’! units, “<=600”), which returns the value of 10.

11. COUNTIF from different worksheet of different Workbook:

In our example, we are using two different workbooks–singer data and COUNTIF. We are working on COUNTIF workbook; however, we want to count how many times the singer “Taylor” is present in the singer_data workbook. To do so, we build the formula as follows:

Step 1: Start the formula with an equal sign as usual:

=COUNTIF(

Step 2: Reference to the workbook:

=COUNTIF([singer_data.xlsx] // the reference to different workbook is enclosed by bracket [ ]//

Step 3: Reference to the worksheet of the workbook:

=COUNTIF([singer_data.xlsx] singer! // here, singer is the name of the worksheet from singer_data workbook//

Step 4: Reference to the cells:

=COUNTIF([singer_data.xlsx] singer! $D$2:$D$1217 //Cell reference of the minute column//

Step 5: Add Cell criteria and close the formula with parenthesis:

=COUNTIF([singer_data.xlsx]singer!$D$2:$D$1217, “>4”) //We want to know how many songs were sung more than 4 minutes//

The final formula is =COUNTIF([singer_data.xlsx]singer!$D$2:$D$1217, “>4”), which returns the value of 612, suggesting that 612 songs were sung more than 4 minutes.

Here are the example questions and answers from the Singer_data set.

More related readings:

COUNTIF MS Office Post

SUMIF Function

SUM Function

Excel SOLVER Example

Benford’s Law in Excel