# Excel SUMIFS Function

SUMIFS is a function to sum cells that meet multiple criteria. SUMIFS can be used to sum values when corresponding cells meet criteria based on dates, numbers, and text. SUMIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

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

The SUMIFS function sums cells in a range using supplied criteria. Unlike the SUMIF function, SUMIFS can apply more than one set of criteria, with more than one range. The first range is the range to be summed. The criteria are supplied in *pairs* (range/criteria) and only the first pair is required. To apply additional criteria, provide an additional range/criteria pair. Up to 127 range/criteria pairs are allowed.

Criteria can include logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Criteria can also be based on a value from another cell, as explained below.

SUMIFS is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and SUMIFS *requires* a cell range for range arguments, you can't use an array.

If you need to manipulate values that appear in a *range* argument (i.e. extract the year from dates to use in criteria), see the SUMPRODUCT and/or FILTER functions.

### Examples

For a single condition, the general pattern for SUMIFS is:

=SUMIFS(sum_range,range1,criteria1) // 1 condition

When cells in *range1* meet *criteria1*, corresponding cells in *sum_range* are summed. For two conditions, the pattern is:

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

Notice criteria are provided in range/criteria pairs. When cells in *range1* meet *criteria1*, __and__ cells in *range2* meet *criteria2*, corresponding cells in *sum_range* are summed. The same pattern is extended to handle more conditions.

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

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".` `

### Notes

- Multiple conditions are applied using AND logic, i.e. condition 1
*AND*condition 2, etc. - Each additional range must have the same number of rows and columns as
*sum_range*, but ranges do not need to be adjacent. If you supply ranges that don't match, you'll get a #VALUE error. - Text strings in criteria must be enclosed in double quotes (""), i.e. "apple", ">32", "jap*"
- 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 find a literal question mark or asterisk, use a tilde (~) in front of the question mark or asterisk (i.e. ~?, ~*).
- SUMIF and SUMIFS can handle ranges, but not arrays. This means you can't use other functions like YEAR on the criteria range, since the result is an array. If you need this functionality, use the SUMPRODUCT function.
- The order of arguments is different between the SUMIFS and SUMIF functions.
*Sum_range*is the__first__argument in SUMIFS, but the__third__argument in SUMIF.

## Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.