SUMIFS Function in Excel – Calculate the sum on multiple conditions

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

SUMIFS Function in Excel 1

  • 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

Result:

SUMIFS Function in Excel 2

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