SUMIFS function

This article describes the formula syntax and usage of the SUMIFS function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

Description

Adds the cells in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet multiple criteria. For example, if you want to sum the numbers in the range A1:A20 only if the corresponding numbers in B1:B20 are greater than zero (0) and the corresponding numbers in C1:C20 are less than 10, you can use the following formula:

=SUMIFS(A1:A20, B1:B20, ">0", C1:C20, "<10")

 Important   The order of arguments differ between the SUMIFS and SUMIF functions. In particular, the sum_range argument is the first argument in SUMIFS, but it is the third argument in SUMIF. If you are copying and editing these similar functions, make sure you put the arguments in the correct order.

Syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

The SUMIFS function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Remarks

  • Each cell in the Sum_range argument is summed only if all of the corresponding criteria specified are true for that cell. For example, suppose that a formula contains two Criteria_range arguments. If the first cell of Criteria_range1 meets Criteria1, and the first cell of Criteria_range2 meets Critera2, the first cell of Sum_range is added to the sum, and so on, for the remaining cells in the specified ranges.
  • Cells in the Sum_range argument that contain TRUE evaluate to 1; cells in Sum_range that contain FALSE evaluate to 0 (zero).
  • Unlike the range and criteria arguments in the SUMIF function, in the SUMIFS function, each Criteria_range argument must contain the same number of rows and columns as the Sum_range argument.
  • You can use the wildcard characters — the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Examples

The workbook below shows examples of this function. Inspect them, change existing formulas, or enter your own formulas to learn how the function works.

To work more in-depth with the example data in Excel, download the embedded workbook to your computer, and then open it in Excel.

Example 2

Example 3

Example 4

Example 5

Top of Page Top of Page

 
 
Applies to:
Excel 2013, Excel Online