As a result, the task of defining criteria in SUMIFS can be a bit tricky.
Each condition is provided with a separaterangeandcriteria.
The generic syntax for SUMIFS looks like this:
Note thesum_rangealways comes first.
This is the range of cells to sum.
Each condition is provided as a pair of range/criteria arguments.
The first formula above defines one condition and the second defines two.
Additional conditions are defined by additionalrange/criteriapairs.
The tricky part is the syntax needed to apply the criteria.
This is because SUMIFS is in a group ofeight functionsthat split logical criteria into two parts:rangeandcriteria.
Because of this design, operators must be enclosed in double quotes ("").
This makes it easy to change criteria later without editing the original formula.
Notice the greater than operator (>), which is text, must be enclosed in quotes.
to match a question mark (?)
that occurs at the end of “Montana?”
The tilde (~) is an escape character that allows you to find aliteralwildcard.
To match a question mark (?
), use “~?
For more details,see this example.
Summary Table
you’re free to use SUMIFS to create a simple summary table.
In the worksheet below, we have a list of unique colors in F5:F9.
The goal is to subtotal the amounts in column D by color.
For more advanced users, this is an important limitation in how SUMIFS works and what it can do.
One of the more tricky limitations of SUMIFS is that it won’t allow anarrayfor arangeargument.
This formula works fine, but it’s a little complicated.
This would be cool if it worked.
However, Excel won’t even let you enter this formula.
However, SUMIFS is not programmed to handle arrays, so it won’t work.
How can we work around this problem?
If the external workbook is not open, you will see a #VALUE!
As a workaround, you could switch to the SUMPRODUCT function, which does not have this limitation.
The most common way to work around most of these limitations is to use theSUMPRODUCT function.
Criteria can be applied to dates, numbers, and text.
for partial matching….
SUMPRODUCT Function
The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.
