Math and trigonometry
SUMIFS
Function
Adds the cells in one
or more rows or columns specified by multiple criteria
by
Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

Adds the cells in one or more rows or columns specified by multiple criteria in parallel rows or columns.
Managers... 
Charley Kyd can
personally help you to apply the Excel methods in this
article to your own organization.
Click here to learn more. 
For example, if you want to sum the numbers in the range A1:A10 only if the corresponding numbers in B1:B10
equal zero (0) and the corresponding numbers in C1:C10 are less than 100, you can use the following formula:
=SUMIFS(A1:A10, B1:B10, 0, C1:C10, "<100")
Syntax
SUMIFS(sum_range, criteria_range, criteria, ...)
 sum_range Required. One or more cells to sum
in a row or column, including numbers,
range names, or cell references that contain numbers. Blank and text values are ignored.
 criteria_range Required. The first range in which to evaluate the associated criteria.
Error values in the criteria_range are ignored.
 criteria Required. The criteria in the form of a number, expression, cell reference, or text that define which cells in the Criteria_range1 argument will be added. For example, criteria can be expressed as
99, ">99", B4, "sales", or "99".
 ... Optional. Repeated pairs of criteria_range and criteria
arguments, to a total of 127 pairs.
Applies To
Excel 2007 and above
Remarks
 SUMIFS can't return results from a closed external
workbook.
 The sum_range must have the same number of rows and columns
as all criteria_ranges, as shown in Examples 2 and 3 below.
 SUMIFS allows the use of the
wildcard characters "*"
(asterisk) and "?" (question mark) in a criteria. See
Example 5.
 Unlike the way that the
SEARCH function uses wildcards, any number in a SUMIFS
criteria range will be ignored. (See Example 6
below.)
 The Microsoft Help topic for SUMIFS states that cells in the Sum_range argument that contain TRUE evaluate to 1; cells in Sum_range that contain FALSE evaluate to 0 (zero).
However, as Example 4 illustrates, both TRUE and FALSE
actually evaluate to zero.
Examples
You can
download this example workbook here,
along with all other example workbooks I've completed for this
Excel help area.
Example 1:
The
SUMIFS function returns the sum of the one value in cell A20,
which is the only cell that satisfies both criteria.
Example 2:
The SUMIFS function returns an error value because the SumRange
occupies two columns and the criteria ranges occupy only one
column each.
Example 3:
The SUMIFS function returns the sum of cells A3 and B4, which
are the two cells that satisfy all criteria. Unlike
Example 2, an error value isn't returned because the SumRange
and the criteria ranges have the same size and shape.
Example 4:
Both TRUE and FALSE in the SumRange resolve to zero, as this
example illustrates. Note that the following Other Help topic from
Microsoft says that TRUE resolves to 1.
Example 5:
A question mark matches any single character; an asterisk
matches any sequence of characters. Here, only cell A2 matches
both criteria.
Example 6
Unlike the SEARCH function,
the SUMIFS function uses wildcards only against text. It ignores
numeric data entirely.
Other Help
