For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

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, 2005-2014
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

 

Charley's SwipeFile charts


Free Excel Dashboards