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:
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:
Ready to know how they work? Let’s go!
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:
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.
Note: Be careful when entering the criteria. You must type the exact text. The minimum character will change the whole result.
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).
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).
The logical expressions respond to the standard logical mathematical operators:
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:
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:
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).
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;“”).
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!