This syntax takes a little getting used to.
See below for many working examples.
When cells meet the criteria, corresponding cells in thesum_rangeare summed.
Ifsum_rangeis omitted, the cells inrangeare summed instead.
In the worksheet below, we have a small amount of sales data.
The tricky part about using the SUMIF function is the syntax needed to apply criteria.
This is because SUMIF 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.
The most reliable way to do this is to refer to avalid datein another cell or use theDATE function.
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 could use SUMIF 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 SUMIF works and what it can do.
One of the more tricky limitations of SUMIF is that it won’t allow anarrayfor arangeargument.
This would be cool if it worked.
However, Excel won’t even let you enter this formula.
If you try, you’ll get a generic “There’s a problem with your formula” error.
However, SUMIF is not programmed to handle arrays, so it won’t work.
How can we work around this problem?
However, the latest version of Excel offers powerful alternatives, includingBYROW,BYCOL, andGROUPBY.
SUMPRODUCT Function
The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.
