How to use COUNTIF function in Excel

Have you ever wanted to know and locate a specific type of cell, but you can’t because of the vast amount of values you have on it? Now you can do so, thanks to the COUNTIF function! 

This tool allows you to count the number of cells containing criteria or conditions and can be very convenient when you need to give your boss a specific value from the data you have on your worksheet.

So, the COUNTIF function works with two arguments: Range and criteria. Both of them are essential to work within the next syntaxis:

=COUNTIF(range, criteria)

• Range: This is the group of cells you want to report on.
• Criteria: Is the matching text, number, or expression within the cell range to a specific condition.

But of course, there are distinct criteria types, and each of them works differently from the others, and its writing changes a little bit in the syntaxis. So here are the main 4 criteria you can use in Microsoft Excel:

• Text Criteria:
• Cell reference Criteria:
• Numeric Criteria:
• Logical Expression Criteria:
• Finding Blanck Cells:

Ready to know how they work? Let’s go!

Text Criteria:

Alright, this function works when you want to find a specific name or content written as text. So, the syntaxis will be:

=COUNTIF(Range;“Criteria”). Example: =COUNTIF(A1:A11;”Jane Doe”).

To use them properly, you can follow the steps below:

1. First, pick a cell apart from the table.
2. Next, type the syntaxis, remembering to open the parenthesis.
3. Select the cell range.

Note: If you have a table, you can choose the whole data column by typing the name of the table and the column header between brackets.

4. Type the semicolon icon to separate the range from the data.
5. Enter the criteria you need between quote marks.
6. Close the parenthesis.
7. Hit the Enter key and get your result.

Note: Be careful when entering the criteria. You must type the exact text. The minimum character will change the whole result.

Cell reference Criteria:

On the other hand, the cell reference criteria are when you can search for a specific value or text from the data set simply by using another cell reference. Check out how you can write it:

=COUNTIF(Range;Cell reference). Example: =COUNTIF(A1:A11;C4).

Numeric Criteria:

This can be handier when working with numerical values, not only whole numbers but also prices, liters, or any numerical measurement. You only need to type this syntaxis:

=COUNTIF(Range;The particular number you’re looking for). Example: =COUNTIF(A1:A11;20).

Logical Expression Criteria:

The logical expressions respond to the standard logical mathematical operators:

• >= Greater than or equal to.
• <= Less than or equal to.
• <> Not equal to.

Although the argument says “Equal to,” you don’t need to type the equal sign due is implicit. And in the other hand, you can use these operators and combine them to make more specific operations as the following:

Logical expression with a numeric value:

This operation works better when looking for a range and not anything specific. Hence, as the Numeric Criteria, this can be applied to whole numbers, prices, or any type of number. Moreover, you have two syntaxis by which you can access this operation:

• =COUNTIF(Range;”LogicalOperator and Number”). Example: =COUNTIF(A1:A11;”>3”).

• =COUNTIF(Range;”LogicalOperator”&Number). Example: =COUNTIF(A1:A11;”<”&4).

Logical expression with a cell reference:

This expression only works when using the logical operator enclosed between quote marks followed by an ampersand icon (&) and selecting the cell reference. Check the syntaxis:

=COUNTIF(Range;”LogicalOperator”&Cell reference). Example: =COINTIF(A1:A11;”>”&C4).

Finding Blanck Cells:

Also, this fantastic tool allows you to find those blank cells in your data set. You only need to type this syntaxis:

=COUNTIF(Range;“”). Example: =COUNTIF(A1:A11;“”).

Do you want to know more?

Now you’ve learned how to use a COUNTIF function! All that remains is to try it yourself and find more uses for this aid! But if you need something advanced, you can learn about the COUNTIFS function, which despite seeming to be the same operation, has some variations. Also, you can use the Pivot Table o the Slicers when using this amazing tool. Finally, remember to check out our Excel course here in Skillademia!