## Purpose

## Return value

## Syntax

`=SUMIFS(sum_range,range1,criteria1,[range2],[criteria2],...)`

*sum_range*- The range to be summed.*range1*- The first range to evaluate.*criteria1*- The criteria to use on range1.*range2*- [optional] The second range to evaluate.*criteria2*- [optional] The criteria to use on range2.

## How to use

The SUMIFS function sums the cells in a range that meet multiple conditions, referred to as *criteria*. Each condition is provided with a separate *range* and *criteria*. To define criteria, SUMIFS supports various logical operators (>,<,<>,=) and wildcards (*,?,~). The SUMIFS function is a widely used function in Excel and can be used to sum cells based on dates, text values, and numbers. However, the syntax used to apply conditions is a bit tricky because it is unusual in Excel. See below for details.

### Syntax

The syntax for the SUMIFS function depends on the criteria being evaluated. Each condition is provided with a separate *range* and *criteria*. The generic syntax for SUMIFS looks like this:

```
=SUMIFS(sum_range,range1,criteria1) // 1 condition
=SUMIFS(sum_range,range1,criteria1,range2,criteria2) // 2 conditions
```

The first argument, *sum_range*, is the range of cells to sum, which should contain numeric values. The second argument, *range1*, is the range to which the first condition should be applied. The third argument, *criteria1*, contains the condition that should be applied to *range1*, along with any logical operators. Additional conditions are applied by providing additional *range/criteria* arguments. When using SUMIFS, keep the following in mind:

- To be included in the final result,
*all conditions must be TRUE*. - All
*ranges*must be the same size or SUMIFS will return a #VALUE! error. *Criteria*should include logical operators (>,<,<>,<=,>=) as needed.- Each new condition requires a separate
*range*and*criteria*.

### Applying criteria

The SUMIFS function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Because SUMIFS is in a group of eight functions that split logical criteria into two parts, the syntax is a bit tricky. Each condition requires a separate *range* and *criteria*, and operators need to be enclosed in double quotes (""). The table below shows some common examples:

Target | Criteria |
---|---|

Cells greater than 75 | ">75" |

Cells equal to 100 | 100 or "100" |

Cells less than or equal to 100 | "<=100" |

Cells equal to "Red" | "red" |

Cells not equal to "Red" | "<>red" |

Cells that are blank "" | "" |

Cells that are not blank |
"<>" |

Cells that begin with "X" | "x*" |

Cells less than A1 | "<"&A1 |

Cells less than today | "<"&TODAY() |

Notice the last two examples use concatenation with the ampersand (&) character. When a criteria argument includes a value from another cell, or the result of a formula, logical operators like "<" must be joined with concatenation. This is because Excel needs to evaluate cell references and formulas first in order to get a value, before that value can be joined with an operator.

### Basic usage

With numbers in the range A1:A10, you can use SUMIFS to sum cells greater than 5 like this:

`=SUMIFS(A1:A10,A1:A10,">5")`

If the range B1:B10 contains color names like "red", "blue", and "green", you can use SUMIF to sum numbers in A1:A10 when the color in B1:B10 is "red" like this:

`=SUMIFS(A1:A10,B1:B10,"red")`

Notice A1:A10 remains the *sum_range*, but range1 and criteria1 have been changed to apply the condition that cells in B1:B10 must be equal to "red". To sum cells in A1:A10 that are greater than 5 only when cells in B1:B10 equal "red", you can use SUMIF like this:

`=SUMIFS(A1:A10,A1:A10,">5",B1:B10,"red")`

SUMIFS is now using five arguments: *sum_range* is A1:A10, *range1* is A1:A10 and *criteria1* is ">5", and *range2* is B1:B10 and *criteria2* is "red".

### Worksheet example

In the worksheet shown, there are two SUMIFS formulas. In the first example (I5), SUMIFS returns a sum of values in column F where the color in column C is "red". In the second example (I6), SUMIFS sums values in column F when the color is "red" and the state is Texas (TX):

```
=SUMIFS(F5:F15,C5:C15,"red") // color="red"
=SUMIFS(F5:F15,C5:C15,"red",D5:D15,"TX") // color="red", state="TX"
```

Notice the equals sign (=) is not required when constructing "is equal to" criteria. Also notice SUMIFS is not case-sensitive; you can use "red" or "Red", and "TX" or "tx".` `

### Limitations

The SUMIFS function has some limitations you should be aware of:

- Conditions in SUMIFS are joined by AND logic. In other words, all conditions must be TRUE in order for a cell to be included in a sum. To sum cells with OR logic, you can use a workaround in simple cases.
- All ranges must be the same size. If you supply ranges that don't match, you'll get a #VALUE error.
- The SUMIFS function requires actual
*ranges*for all range arguments; you can't use an array. This means you can't do things like extract the year from dates inside the SUMIFS function. To alter values that appear in a*range*argument before applying criteria, the SUMPRODUCT function is a flexible solution. - SUMIFS is not case-sensitive. To sum values based on a case-sensitive condition, you can use a formula based on the SUMPRODUCT function with the EXACT function.
- SUMIFS has some other quirks, which are detailed in this article.

The most common way to work around the limitations above is to use the SUMPRODUCT function. In the current version of Excel, another option is to use the newer BYROW and BYCOL functions.

### Notes

- Multiple conditions are applied using AND logic, i.e. condition 1
*AND*condition 2, etc. - All ranges must be the same size. If you supply ranges that don't match, you'll get a #VALUE error.
- Non-numeric criteria must be enclosed in double quotes (i.e. "<100", ">32", "TX").
- Cell references in criteria are
*not*enclosed in quotes, i.e. "<"&A1 - The wildcard characters "?" and "*" can be used in criteria. A question mark (?) matches any one character and an asterisk (*) matches any sequence of characters (zero or more).
- To match a literal question mark(?) or asterisk (*), use a tilde (~) like (~?, ~*).
- SUMIFS requires a range, you can't substitute an array.