Syntax. COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,...)
Definition. This function counts the number of cells in a range meeting several criteria.
Arguments
criteria_range1 (required). The range in which to evaluate the associated criteria. The cells in the range have to be cell names, cell arrays, or references to cells containing numbers. Empty cells and text values are ignored.
criteria1 (required). The criteria in the form of numbers, expressions, cell references, or text that define which cells will be counted.
criteria_range2 (optional). One to 127 ranges in which the criteria has to be evaluated. The cells in the ranges have to be cell names, cell arrays, or references to cells containing numbers. Empty cells and text values are ignored.
criteria2 (optional). One to 127 criteria in the form of numbers, expressions, cell references, or text that define which cells will be counted.
Note
The cells in a range are counted only if all specified criteria for a cell are met.
If one of the criteria arguments reference an empty cell, the COUNTIFS() function treats the empty cell as a 0 value.
You can use the wildcard characters ? (question mark) and * (asterisk) as criteria. The question mark stands for a character, and the asterisk for a string. If you search for an actual question mark or asterisk, enter a tilde (~) before the character.
Background. The COUNTIFS() function is an extension of the COUNTIF() function. The advantage of COUNTIFS() is that it calculates the number of cells that meet multiple conditions and criteria. You will find more information about the COUNTIF() function in the description of COUNTIF().
Example. The following example again uses the table with the sales of the past two years from the software company (see Figure 12-33).
The goal was to raise the sales to more than $150,000 per month. Now you have to check how often this goal was reached in the past 24 months. To calculate the entire time period, you can use the COUNTIF() function.
However, you want to compare the two years. Therefore, you search for the number of months with sales greater than $150,000 in the years 2007 or 2008.
You use the COUNTIFS() function because this function allows you to specify multiple criteria. In this case, the criteria are >150,000 and 2007 or 2008. Figure 12-34 shows the result:
As you can see in Figure 12-34, the number of months with sales over $150,000 increased to eight in 2008.