What Is COUNTIF Function Excel Formula?

Excel is a highly versatile software. It does more than simple math, but also more complicated formulas. That’s why it is used for various purposes, some of which include accounting, data entry, data management, data analysis, and financial analysis.

‍

This is possible thanks to built-in functions that allow calculations with specified parameters. These functions and formulas help a lot in simplifying complex formulas. One of the most useful functions I’ve found is the Excel COUNTIF function.

‍

Use this function to count the number of cells based on a range of values or a specific value. With the COUNTIF function, you don’t need to count the cells manually on your own. With one or two clicks, you can get the numbers you need.

‍

Excel counts for you by using cell references, and it can count cells based on a given criteria. Let's now learn more about what is a cell reference or logical operator, how to use the COUNTIF formula, what are wildcard characters, and how they all come together in Microsoft Excel.

How to Use the COUNTIF Function

As we said, the Excel COUNTIF function counts how many cells are in a range based on the criteria given. No, it doesn’t total the number of cells—it only counts them. If you’ve used Excel before, you have probably been annoyed with how the cells keep totaling the numbers when you need to count them.

‍

The COUNTIF function is exactly what you need for this. Say for example you have a spreadsheet with customer information that includes their name, contact numbers, and addresses complete with zip codes. You want to know how many of these customers are in the same area.

‍

How can you do this without having to check each cell manually? The COUNTIF Excel function is one of the functions that can help you with that. You can literally count the customers from the same area using the zip codes without even sorting them. We’ll show you how to do that, but first, here’s the syntax for this Excel function.

Syntax

= COUNTIF(range, criteria)

• Range 

This is a group of two or more cells, such as cells A2 to C6. In the COUNTIF function, this refers to the number of cells to count.

• Criteria

This is the key concept for the function or formula. When using COUNTIF in Excel, the criteria controls which cells will be counted.

• Return Value

This number refers to the number of cells counted.

How to Use: Step by Step Instructions

‍

1. Start by clicking the cell where you want the return value to appear. In that cell, enter this formula: =COUNTIF()
   
   

2. The next step is to define the range of cells to count by entering two arguments within the parentheses. Remember that the range refers to the cells to count, and criteria control what’s to be counted.

You can do this by placing the cursor inside the parenthesis and then highlighting or manually entering the range of cells. For example, the range could be H6:H21. This means that the range includes values in column H, starting from row 6 to row 21.

The formula should now look like this: =COUNTIF(H6:H21)

3. After this, you need to add a comma after the range to make the new formula like this: =COUNTIF(H6:H21,)

This is when you enter the value or criteria you want for the COUNTIF function. It should tell the formula what you want it to count. Here is how it should look:

=COUNTIF(H6:H21,“marketing manager”)

You can enter text, number, or cell in the criteria. It also doesn’t matter if you enter “Marketing manager,” “marketing manager,” or “MARKETING MANAGER” since the criteria aren’t case-sensitive.

1. The COUNTIF function will activate when you press Enter. It will then show the returned value, which is the number of cells matching your criteria.

‍

How to Apply Criteria

Before you can effectively utilize the COUNTIF function in Excel, you need to understand how to apply the criteria well. For that, remember that this function supports logical operators and wildcards for counting partial matches.

• Logical Operators: > , < , <> , <=  and >=
• Wildcards: * and ?

You see, the application of the right criteria is the tricky part when using the Excel COUNTIF function. It is important that you know exactly what operators to use to achieve the results you want; otherwise, you will be counting the wrong number of cells. Below are some examples of criteria:

Criteria Target

‍

“> 90” - Counts cells with a value greater than 90

50 or “50” - Counts cells with a value equal to 50

“< = 90” - Counts cells with a value less than or equal to 90

“” - Counts blank cells

“< >” - Counts cells that aren’t blank

“blue” - Counts cells with the text “blue,” “Blue,” or “BLUE”

“< >blue” - Counts cells with the text not equal to “blue,” “Blue,” or “BLUE”

‍

Using Double Quotes (““)

You generally don’t need to enclose numbers in double quotes, only text values. The COUNTIF function =COUNTIF(A6:A20, 100) will still work. But if the number includes a logical operator, then you must enclose them in quotes, like so:

=COUNTIF(A6:A20, “>100”)

‍

Counting One Value or Another

It’s not just one value that the COUNTIF function can count. It can also count more than one criterion. For instance, if you want to count the number of customers in either California or Sacramento, you can do that by using two Excel COUNTIF functions.

You will need to use the + sign to do this. The formula should look something like this:

=COUNTIF(A6:A30,“California”)+COUNTIF(A6:A30,“Sacramento”)

Adding another COUNTIF function using the + sign can add more values to this. We can show you how it should work in the samples below.

‍

Using Wildcards

We’ve mentioned wildcards, which are the asterisk (*) and question mark (?) signs. You usually use these characters when not referencing a specific criteria or value. It’s quite useful when you only know a part of the value, meaning you get partial matches.

Using the same list before, you want to count the number of “managers.” Using the wildcard asterisk (*), here’s how the COUNTIF Excel function should look like:

=COUNTIF (H6:H21,“*manager”)

‍

It doesn’t matter whether you put the wildcard at the beginning or end of the value; it will yield the same results. Keep in mind that wildcards don’t work with 3-digit numbers. You can check out support.microsoft.com to learn more about counting cells.

COUNTIF Function Common Use Cases

Here are some more examples of how the COUNTIF function in Excel can be used:

Numeric Criteria

Use the COUNTIF function to count cells greater than, less than, or equal to a value.

‍

1. Count the number of cells greater than a value, i.e., 35.

‍

2. The Excel COUNTIF function below counts the number of cells with a value that is equal to 20.

‍

‍

3. The image below shows the COUNTIF function counting the number of cells with a value less than 85.

‍

‍

4. Here, the COUNTIF function counts the number of cells with a value that’s greater than or equal to 70.

‍

‍

5. Here you can see COUNTIF function counts the number of cells through the value of a specific cell, namely C1. It showed the same results as the example before.

‍

6. Here you can see how Excel COUNTIF can count the number of cells of two values. In this case, 70 and 95.

‍

Text Criteria

1. Here’s an example of the COUNTIF function counting the number of cells for one specific word, “volleyball.”

‍

‍

2. Here’s an example of how to use the COUNTIF function to count the number of cells with the specific word “volleyball” + one character. The wildcard question mark (?) is used to represent this character to search for question mark matches.

‍

‍

3. The Excel COUNTIF function here counts the number of cells with any variation of the specific text, which is “Volleyball.” 

‍

‍

4. This example shows how to use the COUNTIF function to count the number of cells that only have text.

‍

‍

There are a lot more ways to use the COUNTIF function in Excel for whatever applications. We suggest checking Microsoft's official support page for that, or you can always search the web for tips and tricks on counting cells.

COUNTIF in Excel Limitations

The COUNTIF function isn’t perfect. It has limitations, particularly because it can only work with a single condition. If you want to count cells with multiple conditions, the formula you need is the COUNTIFS function.

Some of its other limitations include:

• It can’t count long numbers correctly (15 digits or more)
• It needs a range to work, which you can’t change before you apply criteria.
• It’s not case-sensitive, so the text criteria aren’t as specific.
• It has numerous quirks you might need to know.

The COUNTIFS function can count values based on multiple criteria and offers a more powerful functionality than the COUNTIF function. The COUNTIFS formula considers multiple ranges and determines how many cells are included in it.

When dealing with empty cells, both formulas can take them into account while they count cells based on a more specific condition.

COUNTIF Excel Service by Studyfy

Searching the web looking for help to “Do my Excel homework?” We’re happy to help!

Excel is seemingly an easy-to-learn software. In a way, it is. You can master it with some time and effort even without a tutor or a teacher to help. The web has plenty of articles and videos that teach you how to use Excel if you just take the time to learn.

‍

However, not everyone has that time. Students nowadays have so much on their plate that learning Excel is at the bottom of their goals. But that doesn’t mean you’re off the hook from doing your Excel homework.

‍

Don’t worry because we got your back. Studyfy can help you when you count values, count cells, multiple criteria, search criteria and much more! To count cells manually is hard work, and that+s where Microsoft Excel. wildcard characters. a criteria argument and Studyfy come together.

‍

Studyfy’s here to provide you with the greatest service as an online study help platform. We help with all kinds of homework, including Excel. Whether it’s a COUNTIF function problem or something else, we have professionals ready to assist you.

‍

Need assistance with your Excel worksheet, essay, research paper, dissertation, or any other school work? Contact us today!