The SUMIF function finds the values in a supplied array, that satisfy a given criteria, and returns the sum of the corresponding values in a second supplied array.

Syntax

=SUMIF(range,criteria,[sum_range])

Arguments

Argument Description
range The range of cells that you want evaluated by criteria
criteria The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added

  Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ()
[sum_range] Optional. The actual cells to add, if you want to add cells other than those specified in the range argument

  If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument

Examples

  A B C D E F G
1 Month Agent Sales   Formula Result Notes
2 Jan-15 Norton $22,100   =SUMIF(A2:A9,”Feb-15″,C2:C9) $157,755 Total sales made during February 2015
3 Jan-15 Sam $52,280   =SUMIF(B2:B9,”Sam”,C2:C9) $82,065 Total sales made by Sam during January and February
4 Jan-15 Willie $27,850   =SUMIF(C2:C9,”>30000″) $180,250 Sum of all cells in C2:C9 with values > $30,000
5 Jan-15 Sarah $22,100        
6 Feb-15 Norton $51,280        
7 Feb-15 Sam $29,785        
8 Feb-15 Willie $44,500        
9 Feb-15 Sarah $32,190        

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 supplied criteria is a text string that is more than 255 characters long