Returns the sum of a range that meets a given criteria. The function adds all of its arguments that meet the given criteria.

Sample Usage



SUMIF(range, criteria, [sum_range])

  • range - The range to which the criteria are to be applied.
  • criteria - The pattern or test to apply to the range.
  • sum_range - [OPTIONAL] The range to add up.


SUMIF(B1:B6,">5") returns the sum of such the numbers in the range B1 to B6 that are greater than 5.
SUMIF(B1:B6,">5",C1:C6) returns the sum of such numbers contained in C1:C6 whose corresponding values in range B1:B6 are greater than 5.


  • Cells in range and sum_range that contain TRUE or FALSE are ignored. 
  • Ensure that criteria is enclosed in quotation marks.
  • The range and sum_range need not be of the same size and shape. The actual cells that are summed are determined by using the top, left cell in sum_range as the beginning cell, and then including cells that correspond in size and shape to range. For example:
    • If range is A1:A25 and sum_range is B1:B20, the cells that will be summed are: B1:B25
    • If range is A1:B25 and sum_range is B1:B20, the cells that will be summed are: B1:C25
  • criteria can contain wildcard characters. Currently, the use of asterisk (*) is supported.
    • An asterisk matches any sequence of characters.
    • To match an actual asterisk, type a tilde (~) before the character. For example: (~*).