COUNTIF Function in Excel: bEST 10 Examples

Excel is an extremely good for organizing data in many conditions you want. One of the most popular function to apply conditions and criteria is the COUNTIF function. Now we will look at COUNTIF function and provide useful examples to teach you.

1. What is the excel countif and How to Use the COUNTIF Function in Excel

The COUNTIF function in Excel can count the number of cells. And those can meet a certain condition. The excel countif can be anything. Such as including text, dates, numbers or logical expressions.

2. Syntax of excel countif formula

The syntax of the COUNTIF function is as follows like below.

• range: This is the range of cells that you want to count.
• criteria: While this one is the criteria that you want to when counting.

3. Examples of how to use countif

Example 1: Counting Cells with Numbers with function of countif in excel

Now, we suppose you have a list of numbers in cells A1:A5. And you want to count the number of cells that are greater than 5. If you wanna do this, you can use the following formula:

This formula will count cells in the range A1:A5 that are greater than 5.

Example 2: Counting Cells with Text with countif criteria

Now, if you have a list of names in cells A1:A5 and you want to count the cells having name John. To do this, you can use below one.

This time , the formula will count cells in A1:A5 with John written.

Example 3: Counting Cells with Dates with countif command

If you have dates in cells A1:A5, you can count cells that are after January 1, 2022.

Even it is basic, it will count dates in A1:A5 that are after January 1, 2022.

4. Using Multiple Criteria with countif syntax

You can use multiple criteria with the COUNTIF function by combining the criteria using logical operators such as AND and OR.

Example 4: Counting Cells with Multiple Criteria using

Now with numbers in A1:A5, you can count the cells that are both greater than 5 and less than 10. If you want these conditions in your search, you can use below syntax.

So, this one will count cells in the range A1:A5 that are greater than or equal to 5. And then, it will subtract the number of cells that are greater than 10. The result will be the cells no between 5 and 10.

Example 5: Counting Cells with Multiple Criteria using OR

Again if you have names in cells A1:A5, you want to count the cells that contain either the name John or Jane.

5. Counting Cells with Text using COUNTIF Function

The COUNTIF function can also count cells that contain specific text.

Example 6: Counting Cells with Specific Text

Now, we will imagine you have a list of countries in cells A1:A5. And you want to count the number of cells with text United States. So here is your formula.

You will get the cell no in A1:A5 that contain the text United States.

6. Counting Cells with Dates using COUNTIF Function

This function can work to count cells with specific dates.

Example 7: Counting Cells with Specific Dates

Here, we will suppose we have some dates in cells A1:A5. This time you wanna count the number of cells that are in January 2022. There you got your function below.

You will get the dates in A1:A5 and which are also in January 2022.

7. Using Wildcards with COUNTIF Function

The COUNTIF function can work with wildcards to count cells that match a certain pattern.

Example 8: Counting Cells with Wildcards

In case you have a list of email addresses in cells A1:A5. You can find the ones ending with @gmail.com.

Now you will learn all emails ending with @gmail.com.

8. using COUNTIF Function for Colors

This formula is for counting different colored cells. So you can do it automatically. And you do not have do that by eye.

Example 9: Counting Cells based on Cell Color

Suppose you have a list of cells with different cell colors in cells A1:A5, and you want to count the number of cells that are green. To do this, you can use the following formula:

This formula will count the number of cells in the range A1:A5 that are green.

9. Findind Font Colors with formula

This time, rather than whole cell color, you can count based on font color.

Example 10: Counting Cells based on Font Color

In some reports or tables, you can use different font colors sometimes. Now, if we imagine cells A1:A5  with red font inside, we can apply this formula again. For this specific request, you can apply below formula.

And voila, you do not need to count red fonts by hand.

Conclusion on What is the COUNTIF Function?

The COUNTIF can work miracles for counting cells in Excel based on specific criteria. It can count cells with numbers, text, dates, cell colours, font colours and even more. As you saw above, you can customize this flexible formula on different needs.