In previous articles, we have written a lot about the application of Excel COUNTIF function in specific situations. In today’s article, we will still focus on Excel COUNTIF function, we will introduce you some basic functions of COUNTIF function including the arguments of COUNTIF function, how to enter or load COUNTIF function in Excel workbook, and the many usages of COUNTIF function (what problems it can help us solve). Besides, we will show you some simple cases to create formula with COUNTIF function, how the COUNTIF function can be used in combination with other functions to create formulas, and how the COUNTIF function can be used in life.
This article contains a detailed introduction to the Excel COUNTIF function and a large number of examples of different types of applications. For Excel function beginners, you can bookmark this article. It can help you solve most of the problems in your daily life that can be solved by COUNTIF functions.
If you want to know more about excel functions or others, please bookmark our website, we will continue to provide high quality articles about Excel functions, formulas and applications.
Table of Contents
1. COUNTIF Function Introduction
First of all, we need to know what COUNTIF function is used to do. Excel COUNTIF is a statistical function, it is used for counting the numbers of values from a range or an array that meet a specified condition. COUNTIF function can be used in WPS, Excel, Google sheet versions.
The Syntax of Excel COUNTIF function
Syntax:
=COUNTIF (range, criteria)The Arguments of Excel COUNTIF Function
There are two arguments “Range” and “Criteria”.
- Range: the area you want to count cells
- Criteria: the conditions met by the cell; the conditions can be numbers, text, expressions
2. Enter COUNTIF Function in Excel Worksheet
Step1: Open Excel workbook/worksheet, enter “=COUNTIF” in a blank cell, then COUNTIF function will be loaded with arguments; you can also find the COUNTIF function via Formulas->More Functions->Statistical.
Step2: Select the COUNTIF function, follow the prompts to enter the range and criteria; in “Range” argument, you can drag and drop the range of cells; in “Criteria” argument, you can enter your conditions, if condition is a string or a text or an expression, these conditions should be quoted by (““).
Step3: Press enter after entering function or formula, the result will be displayed.
3. COUNTIF function Usage and Examples
a. Count The Number of Cells of Various Cell Types
According to the type of cells to return the number of cells that meet the conditions. The following examples are often used in our daily work like, you can see if they are cover your cases.
Count the number of blank cells: =COUNTIF(range,””)
Count the number of cells not blank: =COUNTIF(range,”<>”)
Count the number of cells contains text: =COUNTIF(range,”*”)
Although other cells also contain content, but only B2 and B3 belong to the text format, the wildcard character “*” in the condition represents all text.
Count all cells in the selected range: =COUNTIF(range,”<> “””)
b. Count If Cells Greater Than or Less Than a Certain Value
We use a few specific examples to show how to use COUNTIF function to find the number of cells greater than (greater than or equal to), equal to or less than (less than or equal to) a value. In practice, you can replace the values in the examples with your own values.
Count if cells are greater than “90”: =COUNTIF(range,”>90″)
Count if cells are equal to “95”: =COUNTIF(range,”=95″)
Count if cells are less than “90”: =COUNTIF(range,”<90″)
Count if cells are greater than or equal to “85”: =COUNTIF(range,”>=85″)
Count if cells are less than or equal to “95”: =COUNTIF(range,”<=95″)
Count if cells are greater than specific value in B8: =COUNTIF(range,”>”&B8)
Note: If you need to drag and drop the formula to copy the formula in the application, you need to add sign ($) before cell B8 row index and column index to lock the cell, the formula is =COUNTIF (B2:B6, “>”&$B$8)
Count if cells are equal to specific value in B8: =COUNTIF(range,B8)
Count if cells are less than the specific value in B8: =COUNTIF(range,”<“&B8)
Count if cells are greater than or equal to B8: =COUNTIF(range,”>=”&B8)
Count if cells are less than or equal to B8: =COUNTIF(range,”<=”&B8)
c. Count If Cell Contains Text or Cell with Specific Text
This section counts the number of cells that contain text, strings or specific characters. Before introducing the specific examples, we need to briefly understand the concept of wildcards.
A wildcard is a special statement, mainly with an asterisk (*) and a question mark (?) that are used to search files. It can be used in place of one or more real characters when searching for a file; wildcards are often used in place of one or more real characters when the real characters are not known or when you are too lazy to type the full name.
- Asterisk (*) stands for one or more characters
- Question mark (?) stands for only one character
Count if cell contains text “abc”: =COUNTIF(range,”abc”)
Notes:
- Text should be quoted by double quotes, if missing double quotes error #NAME? returns by formula and it signifies some errors should be corrected in this formula.
- Although the strings “abcd” and “abcde” also contain string “abc”, our condition is “abc” and no wildcard character is used to represent other text, so here we want to return the number of cells with the string “abc”.
- COUNTIF () function for the English alphabet is not case-sensitive. So here “ABC” and “abc” meet our confition.
Count if cell contains three characters: =COUNTIF(range,”???”)
The “?” in the condition is a wildcard character; “???” represents three characters, the function means to return the number of cells containing three characters.
Count if cell contains three characters and the second character is a specific character, for example letter “B”: =COUNTIF(range,”?b?”)
Count if cell contains letter “B”: =COUNTIF(range,”*b*”)
Count if cell contains specific text with the second letter is letter “E”: =COUNTIF(range,”?e*”)
Count if cell contains text or value in cell A3: =COUNTIF(range,”*”&A3&”*”)
Count if cell contains four characters of text and text overwrites A2 starting from the second character: =COUNTIF(A1:B4,”?”&A2&”?”)
Count if cell contains A2, and cell overwrites A2 starting from the second character (but no limitation on length): =COUNTIF(A1:B4,”?”&A2&”*”)
In contrast to the above example, this case uses the wildcard character (*) to represent one or more characters concatenated after A2.
These examples above are very basic. Using wildcards in conditional parameters, we can easily search for strings with matching length or characters. Let’s apply the above examples to real work.
Count the number in the “QA” department:
Count the number of males in all departments:
Count the number of employees who passed the exam:
4. COUNTIF Function Used Together with Other Functions
In our work, we often use the COUNTIF function together with other functions to create formulas that can help us solve many problems.
a. COUNTIF with IF Function
Check if the value is duplicated
Determine if the value is the first occurrence
b. COUNTIF with SUMPRODUCT Function
Count number of non-duplicate values
c. COUNTIF with SUM Function
Check if text starts with the specific character
Count the number of cells that satisfy the condition.
For example, count if cell is greater than 70 but less than or equal to 90:
Note: The above example does not require much understanding, such a formula we do not often see in practice, because there are simpler functions that can help us to achieve return the number of cells that meet multiple conditions.
d. COUNTIF with SUM and IF Functions
Check if text contains text a or text b.
5. COUNTIF and COUNTIFs
COUNTIF function has a sister function called COUNTIFS, from the name can be seen COUNTIFS function has a plural (s), COUNT + IFS returns the value of “the number of cells to meet multiple conditions”, it supports multiple conditions compared with the COUNTIF function.
COUNTIFS Function Syntax:
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)Still using the example mentioned above, the formula with COUNTIFs function is very simple and easy to understand. Here the range of data to meet the conditions are the same.
COUNTIFS function supports multiple data range, so we can also set different conditions according to different ranges to return number of cells that meet all conditions.
This article contains almost all the basic uses of the Excel COUNTIF function. In the coming time, we will continue to post the most useful articles about Excel functions. You can bookmark our website, and if you want to learn something about Excel functions or other knowledge, please leave us a message.
6. Related Functions
- Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)… - Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)… - Excel COUNTIFS function
The Excel COUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)… - Excel IF function
- The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
- Excel SUM function
- The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)