SUMIF Function

The SUMIF function adds up numbers in Excel when they meet a specific condition. It's one of Excel's most widely used functions, and you will find it in all kinds of spreadsheets that calculate conditional sums based on dates, text, or numbers. While powerful, SUMIF has a unique syntax that splits logical conditions into two parts, making it different from many other Excel functions. This syntax takes a little getting used to. See below for many working examples.

Key features

• Sums values that meet a single condition
• Works with dates, text, and numbers
• Supports comparison operators (>, <, <>, =) and wildcards (*, ?)
• Available in all Excel versions

Table of Contents

• Basic Example
• Applying Criteria
• Criteria in another cell
• Not equal to
• Blank cells
• Dates
• Wildcards
• OR logic
• Summary Table
• Array Problem
• Limitations
• Notes

Basic Example

The generic syntax for SUMIF looks like this:

=SUMIF(range,criteria,[sum_range])

The criteria is applied to cells in the range. When cells meet the criteria, corresponding cells in the sum_range are summed. The sum_range is optional. If sum_range is omitted, the cells in range are summed instead. In the worksheet below, we have a small amount of sales data. We use SUMIF to perform three calculations: (1) sum all sales by Jim, (2) sum all sales in CA (California), and (3) sum all sales over $100. The formulas in G5:G7 look like this:

=SUMIF(B5:B14,"jim",D5:D14) // sum sales by Jim
=SUMIF(C5:C14,"ca",D5:D14) // sum sales in CA
=SUMIF(D5:D14,">100") // sum sales over 100

Note the following about the formulas above:

• An equal sign (=) is not needed with "is equal to" criteria (i.e. use "jim" not "=jim").
• SUMIF is not case-sensitive, so "jim" and "Jim" will return the same results.
• The last formula in G7 does not provide a sum_range, so range is summed instead.
• Numbers must appear in quotes ("") when used with operators (i.e. ">100").

Applying Criteria

The SUMIF function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. The tricky part about using the SUMIF function is the syntax needed to apply criteria. This is because SUMIF is in a group of eight functions that split logical criteria into two parts: range and criteria. Because of this design, operators must be enclosed in double quotes (""). The table below shows examples of the syntax needed for common criteria:

Notice the last two examples involve concatenation with the ampersand (&) character. Any time you use a value from another cell or the result of a formula with a logical operator like "<", you must concatenate. This is because Excel needs to evaluate cell references and formulas first before the value can used with an operator.

Criteria in another cell

A great way to use SUMIF is to put criteria in another cell and then refer to this cell inside your formula. This makes it easy to change criteria later without editing the original formula. For example, you can sum cells in a range equal to the value in A1 like this:

=SUMIF(range,A1)

If you want to include an operator in the criteria, you will need to concatenate the cell reference to the operator. For example, to sum cells in a range that are greater than A1, use a formula like this:

=SUMIF(range,">"&A1)

Note we are joining the ">" operator to cell A1 with an ampersand (&) character. In the worksheet below, SUMIF has been configured to return the sum of all sales over the value in G4. Notice the greater than operator (>), which is text, must be enclosed in quotes. The formula in G5 is:

=SUMIF(D5:D9,">"&G4) // sum if greater than G4

Not equal to

To express "not equal to" criteria, use the "<>" operator surrounded by double quotes (""). For example, use "<>red" for "not red", and "<>blue" for "not blue", as seen in the worksheet below:

=SUMIF(B5:B9,"<>red",C5:C9) // not equal to "red"
=SUMIF(B5:B9,"<>blue",C5:C9) // not equal to "blue"
=SUMIF(B5:B9,"<>"&E7,C5:C9) // not equal to E7

Note the following:

• In the last formula, we use E7 directly, so we need to concatenate like "<>"&E7.
• Do not use quotes around the cell reference.
• SUMIF is not case-sensitive, so "red", "RED", and "Red" will return the same result.

Blank cells

SUMIF can calculate sums based on whether cells are blank or not blank. Use an empty string ("") to target blank cells and the "not equal to" operator ("<>") to target cells that are not blank. In the worksheet below, SUMIF is used to sum the amounts in column C depending on whether column D contains "x" or is empty:

=SUMIF(D5:D9,"",C5:C9) // blank
=SUMIF(D5:D9,"<>",C5:C9) // not blank

To be more precise, you can use a formula like this that sums values only when column D contains an "x":

=SUMIF(D5:D9,"x",C5:C9) // contains "x"

Dates

In Excel, dates are large serial numbers, so you can use operators like <,>, <=, >= with dates like any other number. The tricky part about using dates in SUMIF conditions is entering the dates in a way that Excel will understand. The most reliable way to do this is to refer to a valid date in another cell or use the DATE function. The example below shows both methods:

=SUMIF(B5:B9,"<"&DATE(2019,3,1),C5:C9)
=SUMIF(B5:B9,">="&DATE(2019,4,1),C5:C9)
=SUMIF(B5:B9,">"&E9,C5:C9)

Note the following:

• When using a function with an operator, you must concatenate like "<"&DATE(2019,3,1)
• When using a cell reference with an operator, you must concatenate like ">"&E9.

Wildcards

The SUMIF function supports three wildcards you can use in criteria for more flexible matching:

• Asterisk (*) - match zero or more characters
• Question mark (?) - match any one character
• Tilde (~) - an escape character to match a literal wildcard

The worksheet below shows how wildcards can be used with the SUMIF function. The formulas in F5:F8 apply the criteria described in column E.

=SUMIF(B5:B11,"mi*",C5:C11) // begins with "mi"
=SUMIF(B5:B11,"*ota",C5:C11) // ends with "ota"
=SUMIF(B5:B11,"????",C5:C11) // contains 4 characters
=SUMIF(B5:B11,"*~?",C5:C11) // ends with "?"

Note the last formula in F8 uses "*~?" to match a question mark (?) that occurs at the end of "Montana?" in cell C10. The tilde (~) is an escape character that allows you to find a literal wildcard. To match a question mark (?), use "~?"; to match an asterisk(*), use "~*"; and to match a tilde (~), use "~~". The table below shows more examples of how wildcards can be used:

OR logic

The SUMIF function is designed to apply just one condition, so there is no obvious way to sum cells with "this OR that" logic. However, one workaround is to provide the criteria as an array constant like {"red","blue}, and then nest the SUMIF formula inside the SUM function like this:

=SUM(SUMIF(range,{"red","blue"},sum_range))) // red or blue

The formula above will sum cells in sum_range when cells in range contain "red" or "blue". Essentially, SUMIF returns two sums in an array (one for "red" and one for "blue"), and the SUM function returns the sum of the sums. For more details, see this example.

Summary Table

You can 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. The formula in cell G5, copied down, is:

=SUMIF($C$5:$C$16,F5,$D$5:$D$16)

Notice that the range and the sum_range are locked as absolute references to prevent changes as the formula is copied down the column. If you are using Excel 2021 or later, you can generate all totals at once with a dynamic array formula like this:

=SUMIF(C5:C16,F5:F9,D5:D16)

We don't need the absolute references in this case because a single formula creates all results. You can go one step further by using the UNIQUE function in cell F5 to get a list of unique colors, then referring to the spill range directly like this:

=SUMIF(C5:C16,F5#,D5:D16)

The advantage of using the spill range is that the sums will update if the list of unique colors changes.

Array Problem

Note: this is an advanced topic, and you don't need to understand it if you are just learning about Excel formulas. 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 an array for a range argument. To understand the problem, consider the worksheet below, where we have 12 dates in column B and 12 amounts in column C. The goal is to create a formula to sum the amounts by year. If you have some experience with Excel formulas, you might think you can use SUMIF and YEAR together in a clever formula like this:

=SUMIF(YEAR(B5:B16),E5,C5:C16)

The idea is to extract the year from the dates in column B with the YEAR function and then use 2024 in cell E5 for the criteria. 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.

The problem is that SUMIF requires a proper range for the range argument, but YEAR(B5:B16) will return an array like this:

{2024;2024;2024;2024;2024;2025;2025;2025;2025;2025;2025;2025}

To be clear, using YEAR like this works fine in most other formulas. However, SUMIF is not programmed to handle arrays, so it won't work. How can we work around this problem? One nice solution is to switch to the SUMPRODUCT function and use a formula like this:

=SUMPRODUCT(sum_range,--(range=criteria))

If we adapt the pattern above to the workbook example, we get the following:

=SUMPRODUCT(C5:C16,--(YEAR(B5:B16)=E5))

Note: I would normally use absolute references for the two ranges so that the formula can be copied down without changes, but I have left the addresses relative here to make the formula easier to read.

Another option is to switch to the SUMIFS function and use two conditions to capture all dates in a given year with a formula like this:

=SUMIFS(C5:C16,B5:B16,">="&DATE(E5,1,1),B5:B16,"<="&DATE(E5,12,31))

However, this is undeniably a more complicated formula, and I prefer the SUMPRODUCT option. In fact, this is a good example of how SUMPRODUCT can often solve a tricky problem in a clever and elegant formula.

Limitations

The SUMIF function has several limitations you should be aware of:

• SUMIF only supports a single condition. To count cells that meet multiple conditions, use the SUMIFS function.
• SUMIF is not case-sensitive. Use the EXACT function with SUMPRODUCT to create a case-sensitive sum.
• SUMIF requires an actual range for the range argument; you can't provide an array. This means you can't alter values in a range inside your formula before applying criteria. Read more here.
• If you reference a range in an external workbook with SUMIF, the workbook must be open, or SUMIF will return a #VALUE! error. To workaround this problem, you can switch to the SUMPRODUCT function, which does not have this limitation. The syntax will look like this: =SUMPRODUCT(--(criteria_range=criteria),sum_range). See this page for details.
• SUMIF assumes the sum_range is the same size as the range and will silently resize the sum_range when necessary to match the range argument, using the upper left cell in the range as an origin. This can create incorrect results that look "normal". For an example of this problem, see this article.
• SUMIF has other quirks, which are explained in this article.

The most common way to work around most of these limitations is to use the SUMPRODUCT function. However, the latest version of Excel offers powerful alternatives, including BYROW, BYCOL, and GROUPBY.

Notes

• SUMIF only supports one condition. Use the SUMIFS function for multiple criteria.
• When sum_range is omitted, the cells in range will be summed.
• 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 (~?, ~*, ~~).
• SUMIF requires a range; you can't substitute an array.