How to use SUMIF function in Excel with formula examples

SUMIF greater than or less than

To sum numbers greater than or less than a particular value, configure the SUMIF criteria with one of the following logical operators:

• Greater than (>)
• Greater than or equal to (>=)
• Less than (<)
• Less than or equal to (<=)

In the table below, supposing you wish to add up the sales numbers for the items that ship in 3 or more days. To express this condition, put a comparison operator (>) before the number and surround the construction in double quotes:

=SUMIF(C2:C10, ">3", B2:B10)

If the target number is in another cell, say F1, concatenate the logical operator and cell reference:

=SUMIF(C2:C10, ">"&F1, B2:B10)

In a similar manner, you can sum values smaller than a given number. For this, use the less than (<) operator:

=SUMIF(C2:C10, "<3", B2:B10)

SUM IF equal to

A SUMIF formula with the "equal to" criteria works for both numbers and text. In such criteria, the equals sign is not actually required.

For instance, to find a total of the items that ship in 3 days, either of the below formulas will do:

=SUMIF(C2:C10, 3, B2:B10)

or

=SUMIF(C2:C10, "=3", B2:B10)

To sum if equal to cell, supply a cell reference for criteria:

=SUMIF(C2:C10, F1, B2:B10)

Where B2:B10 are the amounts, C2:C10 is the shipment duration, and F1 is the desired delivery time.

Likewise, you can use the "equal to" criteria with text values. For instance, to add up the Apples amounts, choose any of the formulas below:

=SUMIF(A2:A10, "apples", B2:B10)

=SUMIF(A2:A10, "=apples", B2:B10)

=SUMIF(A2:A10, F1, B2:B10)

Where A2:A10 is the list of items to compare against the value in F1.

The above formulas imply that the criterion matches the entire cell contents. Consequently, the SUMIF function will add up Apples sales but not, say, Green Apples. To sum partial matches, construct the "if cell contains" criteria like in this SUMIF wildcard formula.

SUM IF not equal to

To build the "not equal to" criteria, use the "<>" logical operator.

When a value, either text or number, is hardcoded in the criteria, remember to surround the entire construction with double quotes.

For example, to sum the amounts with shipment other than 3 days, the formula goes as follows:

=SUMIF(C2:C10, "<>3", B2:B10)

To find a total of all the items except Apples, the formula is:

=SUMIF(A2:A10, "<>apples", B2:B10)

When the criterion is in another cell, concatenate the "not equal to" operator and a cell reference like this:

=SUMIF(A2:A10, "<>"&F1, B2:B10)

SUM IF blank

This example shows how to sum cells in one column if a corresponding cell in another column is blank. There are two formulas to fulfill the task. Which one to use depends on your interpretation of a "blank cell".

If "blank" means cells that contain absolutely nothing (no formula, no zero-length string returned by some other function), then use "=" for criteria. For example:

=SUMIF(B2:B10, "=", C2:D10)

If "blank" includes empty strings (for example, cells with a formula like =""), then use "" for criteria:

=SUMIF(B2:B10, "", C2:D10)

Both formulas return a total of sales for undefined regions, i.e. where a cell in column B is blank:

SUM IF not blank

To make "if cell is not blank then sum" kind of formula, use "<>" as the criteria. This will add up all cells that contain anything in them, including zero-length strings.

For instance, here's how you can sum sales for all the regions, i.e. where column B is not blank:

=SUMIF(B2:B10, "<>", C2:D10)

Excel SUMIF with text criteria

When adding up numbers in one column based on text values in another column, it's important to differentiate between exact and partial match.

For real-life formula examples, please check out Sum if equal to and Sum if not equal to.

In the next section, we'll take a closer look at SUMIF formulas with partial match.

Example 1. Sum values based on partial match

Suppose you wish to total sales for all northern regions, including North, North-East, and North-West. To have it done, put an asterisk right after the word "north":

=SUMIF(B2:B10, "north*", C2:D10)

An asterisk on both sides will also work:

=SUMIF(B2:B10, "*north*", C2:D10)

Alternatively, you can type the region of interest in a predefined cell (F1), and then concatenate a cell reference and a wildcard character enclosed in quotes:

=SUMIF(B2:B10, F1&"*", C2:D10)

Or

=SUMIF(B2:B10, "*"&F1&"*", C2:D10)

Example 2. Sum if cell contains * or ?

To match a literal question mark or asterisk, place a tilde (~) before the character, e.g. "~?" or "~*".

For example, to sum sales for the regions marked with *, use "*~*" for criteria. In this case, the first asterisk is a wildcard and the second one is a literal asterisk character:

=SUMIF(B2:B10, "*~*", C2:D10)

If the criteria (* in our case) is entered in a separate cell, then concatenate a tilde and the cell reference, like this:

=SUMIF(B2:B10, "*"&"~"&F1, C2:D10)

Example 3. Sum if another cell contains text

If your dataset contains various data types and you only want to sum cells corresponding to text values, the following SUMIF formulas will come in handy.

To add up values in cells C2:C8 if a cell in column A contains any text character(s):

=SUMIF(A2:A8,"?*", C2:C8)

To sum values in C2:C8 if a cell in column A contains any text value, including zero length strings:

=SUMIF(A2:A8,"*", C2:C8)

Both of the above formulas ignore non-text values such as numbers, dates, errors, and Booleans.

1. SUMIF supports only one condition

The syntax of the SUMIF function has room for only one condition. To sum with multiple criteria, either use the SUMIFS function (adds up cells that meet all the conditions) or build a SUMIF formula with multiple OR criteria (sums cells that meet any of the conditions).

2. Range and sum_range should be of the same size

For a SUMIF formula to work correctly, the range and sum_range argument should have the same dimensions, otherwise you may get misleading results. The point is that Microsoft Excel does not rely on the user's ability to provide matching ranges, and to avoid possible inconsistency issues, it determines the sum range automatically in this way:

Sum_range defines only the upper left cell of the range that will be summed, the remaining area is determined by the size and shape of the range argument.

Given the above, the below formula will actually sum cells in C2:C10 and not in C2:D10. Why? Because range consists of 1 column and 9 rows, and so does sum_range.

=SUMIF(B2:B10, "north", C2:D10)

In older Excel versions, unequally sized ranges can cause lots of problems. In modern Excel, complex SUMIF formulas where sum_range has less rows and/or columns than range are also capricious. That is why it's a good practice to always define the same number of rows and columns for these two arguments.

3. Range and sum_range should be ranges, not arrays

Though SUMIF can process an array constant in criteria like shown in this example, it does not support arrays in range and sum_range. These two arguments can only be cell ranges.

5. SUMIF criteria syntax

For criteria, the SUMIF function allows using different data types including text, numbers, dates, cell references, logical operators (>, <, =, <>), wildcard characters (?, *, ~) and other functions. The syntax of such criteria is quite specific.

If the criteria argument includes a text value, wildcard character or logical operator followed by text, number or date, enclose the whole criteria in quotation marks. For example:

=SUMIF(B2:B10, "north*", C2:D10)

=SUMIF(C2:D10, ">100")

=SUMIF(B2:B10, "<>north", C2:D10)

=SUMIF(C2:C10, "<=9/10/2020", B2:B10)

When a logical operator is followed by a cell reference or another function, the criteria should be provided in the form of a string. So, you use an ampersand (&) to concatenate a logical operator and a reference or function. For example:

=SUMIF(B2:B10, ">"&F1)

=SUMIF(C2:D10, "<="&TODAY(), B2:B10)

4. SUMIF from another workbook not working

As with many Excel functions, SUMIF can refer to other sheets and workbooks, provided they are currently open.

For example, this formula will work fine as long as Book1 is open:

=SUMIF([Book1.xlsx]Sheet1!$A$2:$A$9,"apples",[Book1.xlsx]Sheet1!$F$2:$F$9)

And it will stop working as soon as Book1 is closed. This happens because the referenced ranges in closed workbooks get de-referenced into arrays. And since arrays are not supported in the range and sum_range arguments, SUMIF throws a #VALUE! error.

6. SUMIF does not recognize text case

By design, SUMIF in Excel is not case-sensitive, meaning it treats uppercase and lowercase letters as the same characters. To make a case-sensitive SUMIF formula, use the SUMPRODUCT function together with EXACT.