Article updated on June 16, 2018

The SUMIFS function in cell D11 adds numbers from column D based on criteria applied to column B and C.

=SUMIFS(D3:D8,B3:B8,B11,C3:C8,C11)

Excel Function Syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)

Arguments

sum_range Required. A cell reference to a cell range whose numbers you want to sum.
criteria_range1 Required. The cell range you want to test Criteria1 for.
criteria1 Required.  The condition you want to use applied to criteria_range1 to sum the corresponding cells in sum_range
[criteria_range2] Optional.  Up to 127 additional arguments.
[criteria2] Optional.  Up to 127 additional arguments.

 

The SUMIFS function in cell D11 sums all corresponding values that begin with B in column B and is Small in column C.

=SUMIFS(D3:D8,B3:B8,B11,C3:C8,C11)

You can use wildcard characters like:

  • * (asterisk) - Matches any length of characters
  • ? (question mark) - Matches any single character

The formula in cell D11 sums numbers in column D based on numbers less than 104 in column B and Small in column C.

=SUMIFS(D3:D8,B3:B8,B11,C3:C8,C11)

You are also allowed to use logical operators like:

  • > larger than
  • < smaller than
  • = equal to
  • <> not equal to

Download Excel *.xlsx file

SUMIFS function.xlsx