# 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 the*C*argument will be added. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32."*riteria_range1***Criteria_range2, criteria2, …**Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

## Remarks

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

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.