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
- ? – matches any single character
- * – matches any sequence of characters
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:
- 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
- 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
If you want to calculate the sum based on multiple conditions then use SUMIFS Function in Excel, which is discussed in the next chapter.