Related Function:

The SUMIFS function finds values in one or more supplied arrays, that satisfy a set of criteria, and returns the sum of the corresponding values in a further supplied array.

• This function was introduced in Excel 2007 and so is not available in earlier versions.

### Syntax

=SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2], … )

#### Arguments

Argument Description
sum_range An a range of cells containing numbers, which are to be added together if the criteria are satisfied
criteria_range1,
[criteria_range2], …
Ranges of cells containing values to be tested against the respective criteria1criteria2, etc.

 • The supplied criteria_range arrays must all have the same length as the sum_range
criteria1,
[criteria2], …
The conditions to be tested against the values in criteria_range1[criteria_range2], etc.

 • Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks (“)

#### Examples

A B C D E F G
1 Quarter Agent Sales   Formula Result Notes
2 1 Norton \$222,100   =SUMIFS(C2:C13,A2:A13,”>1″,B2:B13,”Sam”) \$768,104 Adds the sales made by Sam for quarters 2, 3, and 4
3 1 Sam \$252,280   =SUMIFS(C2:C13,A2:A13,”<>2″,B2:B13,”Sarah”) \$707,399 Adds the sales by Sarah excluding the 2nd quarter
4 1 Sarah \$222,100
5 2 Norton \$251,280
6 2 Sam \$229,785
7 2 Sarah \$232,190
8 3 Norton \$294,820
9 3 Sam \$273,139
10 3 Sarah \$219,167
11 4 Norton \$258,142
12 4 Sam \$265,180
13 4 Sarah \$266,132

Usage note: You can also use the following wildcards in text matches for the criteria argument:

 ? – matches any single character * – matches any sequence of characters

e.g. the condition “A*e” will match any substring beginning with “A” and ending in “e”.

#### Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if the length of any of the supplied criteria_range arrays is not equal to the length of the sum_range array