SUMIFS Function in Excel finds and returns the sum of array that meets one or more conditions. The SUMIFS function in Excel supports logical operators (>,<,<>,=) and wildcards (*,?) for pattern matching.
Wild cards in SUMIFS 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 SUMIFS Function in Excel:
SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], … )
- sum_range – array of numeric values for which the sum is to be calculated.
- criteria_range1 – Arrays of values to be tested against the respective i.e. criteria1
- criteria1– The criteria or condition on which sum has to be calculated.
Example of SUMIFS Function in Excel
Formula
- First two examples states the use of logical operation in SUMIFS function.
- Last two examples states the use of wildcards in SUMIFS function.
So the result will be like