Function: SUMIFS

by | Oct 24, 2018 | Excel Formulas

SUMIFS Example 2

SUMIFS function can sum up the values in a range of cells, based on some criteria. In this guide, we’re going to show you how to use the SUMIFS function and cover some examples and error handling methods.

Supported versions

  • (Windows) Excel 2007 and above
  • (Mac) Excel for Mac 2011 and above
  • Web and mobile versions

SUMIFS Syntax

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

Arguments

sum_range The range of cells to sum
criteria_range1 The range of cells that you want to apply the criteria1 against
criteria1 The criteria that is applied to criteria_range1 to define which cells to add
[criteria_range2, criteria2] Optional. Additional ranges and their associated criteria pairs. You can enter up to 127 range/criteria pairs.

Examples

Here, we used named ranges to make the formulas easier to read. This is not required.

Example 1

=SUMIFS(Total,Type,"WATER",Attack,">=50",Sp.Atk,"<100")
 formula adds Total values where Type is equal to "WATER", Attack values are equal or greater than 50 and Sp. Atk values are less than 100.

SUMIFS Example 1

Note: SUMIFS function is not case-sensitive. Therefore, "WATER" and "water" criteria will work the same way.

Example 2

=SUMIFS(HP,Type,"*FIRE*",Generation,"<>I")
 formula sums up HP values where Type includes "FIRE" string and Generation is not equal to "I". If we use "FIRE" only without asterisks, the SUMIFS function will skip the "FIRE, GROUND" value.

SUMIFS Example 2

Download Workbook

Tips

  • Use same number of rows and columns for sum and criteria range arguments.
    • Bad Example: =SUMIFS(G2:G15,F2:H10,">2014",J2:J20,"IT")
    • Good Example: =SUMIFS(G2:G11,F2:F11,">2014",J2:J11,"IT")
  • Comparison operators:
Operator Description Criteria Sample Criteria Meaning
= Equal to “=10000” Equal to 10000
<>  Not equal to “<>10000” Not equal to 10000
>  Greater than “>10000” Greater than 10000
<  Less than “>10000” Less than 10000
>= Greater than or equal to “>=10000” Greater than or equal to 10000
<= Less than or equal to “<=10000” Less than or equal to 10000
? Takes the place of a single character “Admin?” 6-character word starts by “Admin”
* Can take the place of any number of characters. “Admin*” Any number of character word starts with “Admin”
~ Use tilde in front of a  question mark or an asterisk to actually find them “Admin~*” Equal to "Admin*"

Note: Wildcards cannot be used for numeric values. Searching a wildcard in a range of numeric values returns no matches.

Issues

#VALUE!

The SUMIFS function returns incorrect results when you use it to match strings longer than 255 characters, or the string #VALUE!.

TRUE and FALSE

TRUE and FALSE values in sum_range are evaluated as numbers. While TRUE is evaluated as 1, FALSE is evaluated as 0. As a result, this condition may cause unexpected results when they are added to other values.