SUMIF Function in Excel

SUMIF Function in Excel finds and returns the sum of supplied array that meets the specific condition. The SUMIF function in Excel supports logical operators (>,<,<>,=) and wildcards (*,?) for pattern matching

Wild cards in SUMIF Function
  • ? –    matches any single character
  • * –    matches any sequence of characters

Example:

The condition “A?e” will match all cells containing a text string beginning with “A” and ending in “e” and only one character in between i.e. A_e.

The condition “A*e” will match all cells containing a text string beginning with “A” and ending in “e” with multiple characters(n) in between i.e. A_______e.

 

Syntax of SUMIF Function in Excel:

SUMIF( range, criteria, [sum_range])
  • range – An Array of range to be tested against the supplied criteria.
  • criteria – The criteria or condition on which sum has to be calculated.
  • [sum_range] – An optional array of numeric values for which the sum is to be calculated.

Note: If the [sum_range] argument is omitted, the sum is calculated for values in the range argument

 

Example of SUMIF Function in Excel

Formula

SUMIF Function in Excel 1

  • First three examples states the use of logical operation in SUMIF function.
  • Last two examples states the use of wildcards in SUMIF function.

Note : in the First Example, the [sum_range] argument is omitted, the sum is calculated for values in the range argument

So the result will be like

Result

SUMIF Function in Excel 2

If you want to calculate the sum based on multiple conditions then use SUMIFS Function in Excel, which is discussed in the next chapter.

previous-small SUMIFS Function in Excel                                                                                                                next_small SUMIFS Function in Excel