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