Syntax. SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...)
Definition. This function adds all numbers in a range matching the search criteria in the criteria ranges.
Arguments
sum_range (required) The cell range in which you want to find the criteria.
criteria_range1 (required) and criteria_range2 (optional) At least one and up to 127 cell ranges in which you want to find the values in criteria1, criteria2, and so on.
criteria1 (required) and criteria2 (optional) The criteria in the form of a number, an expression, or text. The criteria determine which cells will be added. For example, the criteria argument can be expressed as 32, “>32”, “32”, or “apples”.
Background. This function was new in Excel 2007 and finally allows the entry of multiple criteria in a SUMIF() calculation. The SUMIFS() function is an extension of the SUMIF() function and can be used to filter for certain values in a list and add those values. The values in an array are added only if they match the search criteria.
Excel provides additional functions that allow you to analyze data based on a condition. To count text or numbers in an array, use the COUNTIF() function. This function, as well as the AVERAGEIF() and AVERAGEIFS() functions, are explained in Chapter 12.
To return one value or two values based on a condition, such as a sales bonus, use the logical IF() function (see Chapter 9).
Example. Assume that you have to add the sales that took place in a certain month in a list for a company and a product. Figure 16-33 shows the solution.
Cells I7 through I9 contain the filter criteria. Enter the following formula in cell I5:
=SUMIFS(E3:E21,B3:B21,I7,C3:C21,I8,D3:D21,I9)
Specify the array containing the values you want to add (E3:E21). The list is longer than the one shown in Figure 16-33.
The first array contains the criterion you want to filter (B3:B21).
Enter the first search criterion (I7).
Enter the second criteria range (C3:C21), the second criterion (I8), and so on.
In month 3, the Contoso, Ltd. company spent $5,723 on the Desktop PC product.