Syntax
The syntax for the SUMIFS function depends on the number of conditions needed. Each separate condition will require a range and a criteria. The generic syntax for SUMIFS looks like this: The first argument, sum_range, is the range of cells to sum, which should contain numeric values. The second argument, range1, is the range to which the first condition should be applied. The third argument, criteria1, contains the condition that should be applied to range1, along with any logical operators. Additional conditions are applied by providing additional range/criteria arguments. When using SUMIFS, keep the following in mind:
To be included in the final result, all conditions must be TRUE. All ranges must be the same size or SUMIFS will return a #VALUE! error. Criteria should include logical operators (>,<,<>,<=,>=) as needed. Each new condition requires a separate range and criteria.
Applying criteria
The SUMIFS function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Because SUMIFS is in a group of eight functions that split logical criteria into two parts, the syntax is a bit tricky. Each condition requires a separate range and criteria, and operators need to be enclosed in double quotes (""). The table below shows some common examples: Notice the last two examples use concatenation with the ampersand (&) character. When a criteria argument includes a value from another cell, or the result of a formula, logical operators like “<” must be joined with concatenation. This is because Excel needs to evaluate cell references and formulas first in order to get a value, before that value can be joined with an operator.
Basic usage
With numbers in the range A1:A10, you can use SUMIFS to sum cells greater than 5 like this: If the range B1:B10 contains color names like “red”, “blue”, and “green”, you can use SUMIF to sum numbers in A1:A10 when the color in B1:B10 is “red” like this: Notice A1:A10 remains the sum_range, but range1 and criteria1 have been changed to apply the condition that cells in B1:B10 must be equal to “red”. To sum cells in A1:A10 that are greater than zero only when cells in B1:B10 equal “red”, you can use SUMIF like this: SUMIFS is now using five arguments: sum_range is A1:A10, range1 is A1:A10 and criteria1 is “>5”, and range2 is B1:B10 and criteria2 is “red”.
Worksheet example
In the worksheet shown, there are two SUMIFS formulas. In the first example (I5), SUMIFS returns a sum of values in column F where the color in column C is “red”. In the second example (I6), SUMIFS sums values in column F when the color is “red” and the state is Texas (TX): Notice the equals sign (=) is not required when constructing “is equal to” criteria. Also notice SUMIFS is not case-sensitive; you can use “red” or “Red”, and “TX” or “tx”.
Limitations
The SUMIFS function has some limitations you should be aware of:
Conditions in SUMIFS are joined by AND logic. In other words, all conditions must be TRUE in order for a cell to be included in a sum. To sum cells with OR logic, you can use a workaround in simple cases. All ranges must be the same size. If you supply ranges that don’t match, you’ll get a #VALUE error. The SUMIFS function requires actual ranges for all range arguments; you can’t use an array. This means you can’t do things like extract the year from dates inside the SUMIFS function. To alter values that appear in a range argument before applying criteria, the SUMPRODUCT function is a flexible solution. SUMIFS is not case-sensitive. To sum values based on a case-sensitive condition, you can use a formula based on the SUMPRODUCT function with the EXACT function. SUMIFS has some other quirks, which are detailed in this article.
The most common way to work around the limitations above is to use the SUMPRODUCT function. In the current version of Excel, another option is to use the newer BYROW and BYCOL functions.
Notes
Multiple conditions are applied using AND logic, i.e. condition 1 AND condition 2, etc. All ranges must be the same size. If you supply ranges that don’t match, you’ll get a #VALUE error. Non-numeric criteria must be enclosed in double quotes (i.e. “<100”, “>32”, “TX”). Cell references in criteria are not enclosed in quotes, i.e. “<"&A1 The wildcard characters ? and * can be used in criteria. A question mark (?) matches any one character and an asterisk () matches any sequence of characters (zero or more). To match a literal question mark(?) or asterisk (), use a tilde (~) like (~?, ~*). SUMIFS requires a range, you can’t substitute an array.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
