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.
- (Windows) Excel 2007 and above
- (Mac) Excel for Mac 2011 and above
- Web and mobile versions
|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.|
Here, we used named ranges to make the formulas easier to read. This is not required.
Note: SUMIFS function is not case-sensitive. Therefore, “WATER” and “water” criteria will work the same way.
- 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.
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.