Syntax. COUNTIF(range,criteria)
Definition. This function counts the cells within a range whose content matches the search criteria.
Arguments
range (required). The cell range from which you want to count the cells meeting the search criteria.
criteria (required). The criteria in the form of a number, an expression, or text. The criteria determine which cells will be counted. Criteria can be expressed as numbers (such as 2000), text (such as none), or a cell reference (such as B5).
Background. You can analyze your data based on a condition. However, it is not obvious why this function is categorized as a statistic function and not as a math and trigonometry function.
The COUNTIF() function can use only a precise value as search criterion. The attempt to search for cells with values less than 0 fails. You can use < and > only if you enclose these signs and the corresponding value in quotation marks or if you enclose < or > in quotation marks and precede the value with &.
Examples
Example 1. The following example again uses the table with the sales of the past two years from the software company. The goal was to raise the sales to more than $200,000 per month. Now you have to check how often this goal was reached in the last 24 months.
You want to calculate the result by using the COUNTIF() function. For range you enter C3:C26, and for criteria you specify >200000. As shown in Figure 12-31, the result is 15 sales over $200,000.
Tip: Specify several conditions for a data set
Although COUNTIF() works only with one condition, you can specify several conditions for a data set. To use two conditions, you enter one COUNTIF() function for each condition and link these functions with a + (plus sign). In this example, you can search for the values between $180,000 and $200,000 where the first condition is greater than $180,000 and the second condition is less than $200,000.
Enter a COUNTIF() function for each of the two conditions, link the functions with +, and enclose this part of the formula in quotation marks. Then subtract the number of all values in this range from the result to return the numbers greater than 180,000 and less than 200,000.
Example 2. You can also use placeholders with COUNTIF(). Assume that you are the head of the Human Resources department of the software company and create a list for the employees to enter their vacation and flextime. You want to count the flextime days but the employees entered Flexible time day, Flex day, Flex, and FL.
You can enter placeholders because the COUNTIF() function accepts placeholders as search criteria. As shown in Figure 12-32, the * (asterisk) placeholder together with f returns all values beginning with f. In this case, it returns six entries and therefore six flextime days the employees applied for in two days.
