How to use COUNTIF function in Excel?
Contents
Excel COUNTIF function
Description
COUNTIF function is used to count the number of cells with in the range that meets the specific criteria in Excel. This function is categorized as Statistical function in Excel. In this function, we can specify the criteria for different values such as Numeric, Text, Boolean,Dates and Errors. Similarly it allows to use the logical operators (>,<,<>,=) and wildcards(*,?) for matching the values.
Syntax
|
1 |
= COUNTIF(range,criteria) |
Arguments
- range – The range of cell values to check the criteria
- criteria – condition to validate the values in the specified range
Returns
The COUNTIF function returns the numeric value
Countif function examples
The following formulas are used to validate the Age and Gender in this example.
|
1 2 3 |
=COUNTIF(F6:F12,">30") // To get the number of people whose age is greater than 30 =COUNTIF(E6:E12,"Male") // Returns the number of Males =COUNTIF(E6:E12,"Female") // Returns the number of Females |
Double quotes(” “) in the criteria
The criteria needs to mention within the double quotes in the Countif function.However, if we are going to check the equal condition for the numeric values, double quotes is not required in the criteria.
|
1 |
=COUNTIF(F6:F12,"28") // Count the number of people whose age is equal to 28 |
Value from another cell in the criteria
Instead of explicitly mention the values for criteria, we can use another cell value to validate the condition. To mention those values, we need to give the criteria as “<logical_operator>” & cell_location in the COUNTIF function
|
1 |
=COUNTIF(F6:F12,">" &J3) //J3 cell contains the value as 30 to get the count of people |
Wildcards
The wildcard characters such as ?(question mark) and *(asterisk) can be used in the criteria to validate the text values. A question mark matches any one character and an asterisk matches any sequence of characters. See the examples below for Text values
|
1 2 |
=COUNTIF(D6:D12,"?evin") // Result is 1 as the name "Kevin" matched this condition =COUNTIF(D6:D12,"*ry") // Result is 2 as the names such as "Merry & Henry" matched here |
