# 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.):

**sum_range**Required. One or more cells to sum, including numbers or names, ranges, or cell references (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) that contain numbers. Blank and text values are ignored.**criteria_range1**Required. The first range in which to evaluate the associated criteria.**criteria1**Required. The criteria in the form of a number, expression, cell reference, or text that define which cells in theargument will be added. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32."*criteria_range1***criteria_range2, criteria2, …**Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

## Remarks

- Each cell in the
argument is summed only if all of the corresponding criteria specified are true for that cell. For example, suppose that a formula contains two*sum_range*arguments. If the first cell of*criteria_range*meets*criteria_range1*, and the first cell of*criteria1*meets*criteria_range2*, the first cell of*critera2*is added to the sum, and so on, for the remaining cells in the specified ranges.*sum_range* - Cells in the
argument that contain TRUE evaluate to 1; cells in*sum_range*that contain FALSE evaluate to 0 (zero).*sum_range* - Unlike the range and criteria arguments in the
**SUMIF**function, in the**SUMIFS**function, eachargument must contain the same number of rows and columns as the*criteria_range*argument.*sum_range* - 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

### Example 1

Use the embedded workbook shown here to work with examples of this function. You can inspect and change existing formulas, enter your own formulas, and read further information about how the function works.

This example uses the SUMIFS function to add the values of cells that meet specific criteria.

To work in-depth with this workbook, you can download it to your computer and open it in Excel. For more information, see the article Download an embedded workbook from OneDrive and open it on your computer.

### Example 2

This example uses the SUMIFS function to add amounts from bank accounts based on interest paid.

### Example 3

This example uses the SUMIFS function to add rainfall amounts for specific days

### Example 4

This example expands on the data in Example 3, separating the rainfall, average temperatures, and average wind speed into two 12-hour periods for each day.

### Example 5

This example uses the SUMIFS function to enter criteria as a reference or by using wildcard characters