# How to use SUMIF in Excel - formula examples to conditionally sum cells

This tutorial explains the Excel SUMIF function in plain English and provides a numbers of SUMIF formula examples for numbers, text, dates and wildcards.

If you are faced with the task that requires conditional sum in Excel, the SUMIF function is what you need. This tutorial will briefly explain the function's syntax and general usage, and then we will apply the new knowledge in practice by making a few SUMIF formula examples.

A good thing is that the SUMIF function is identical in all Excel versions, from 2016 to 2003. Another great news is that once you've invested some time in learning SUMIF, it will take you very little effort to get the insight of other "IF" functions such as SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF etc.

## SUMIF in Excel - syntax and usage

The SUMIF function, also known as Excel conditional sum, is used to add cells based on a certain condition, or criteria.

If you've happened to read the COUNTIF tutorial on this blog, you won't have any difficulties with understanding Excel SUMIF because its syntax and usage is analogous. So, the syntax of the SUMIF function is as follows:

SUMIF(range, criteria, [sum_range])

As you see, the SUMIF function has 3 arguments - first 2 are required and the 3rd one is optional.

1. `range` - the range of cells to be evaluated by your criteria, for example A1:A10.
2. `criteria` - the condition that must be met. The criteria may be supplied in the form of a number, text, date, logical expression, a cell reference, or another Excel function. For example, you can enter the criteria such as "5", "cherries", "10/25/2014", "<5", "A1", etc.
Note. Please pay attention that any text criteria or criteria containing mathematical symbols must be enclosed in double quotation marks ("). For numerical criteria, double quotation marks are not required.
3. `sum_range` - the cells to sum if the condition is met. This argument is optional, and you need to use it only if you want to sum cells other than defined in the range argument. If the sum_range argument is omitted, Excel will sum the same cells to which the criteria is applied (i.e. specified in the range argument).

To illustrate the Excel SUMIF syntax better, let's consider the following example. Suppose you have a list of products in column A and corresponding amounts in column C. You want to know the sum of all amounts relating to a given product, e.g. bananas.

Now, let's define the arguments for our SUMIF formula:

• range: A2:A8
• criteria: "bananas"
• sum_range: C2:C8

Putting this together, you get: `=SUMIF(A2:A8, "bananas", C2:C8)`

This formula example demonstrates the simplest usage of the SUMIF function with the text criteria. Instead of text, you can include a number, date or a cell references in your criteria. For instance, you can re-write the above formula so that it references the cell containing the name of the product to be summed:

`=SUMIF(A2:A8,F1,C2:C8)`

Note. The sum_range parameter actually specifies only the upper leftmost cell of the range to be summed. The remaining area is defined by the dimensions of the range argument.

In practice, this means that the sum_range argument does not necessarily have to be of the same size as the range argument, i. e. it may have a different number of rows and columns. However, the first cell (i.e. the top left cell) of the sum range must always be the right one. For example, in the above SUMIF formula, you can supply C2, or C2:C4, or even C2:C100 as the sum_range argument, and the result will still be the same. However, the best practice is to provide equally sized range and sum_range.

The point is that Microsoft Excel does not rely on the user's ability to provide matching range and sum_range parameters, so to avoid possible inconsistency issues and prevent errors, it determines the sum range on its own in the following way. It takes the top left cell in the sum_range argument as the beginning cell (C2 in our Excel SUMIF example), and then includes as many columns and rows as contained in the range argument (in our case, it's 1 column x 7 rows, i.e. C2:C8).

## How to use SUMIF in Excel - formula examples

Hopefully, the above example has helped you to gain some basic understanding of how the SUMIF function works. Below you will find a few more formulas that demonstrate how to use SUMIF in Excel with various criteria and on different data sets.

### SUMIF greater than, less than or equal to

Let's have a look at a few SUMIF formulas that you can use to add up values greater than, less than or equal to a given value.

Note. Please pay attention that in Excel SUMIF formulas, a comparison operator followed by a number or text should always be enclosed in double quotes ("").
 Criteria Operator Formula Example Description Sum if greater than > `=SUMIF(A2:A10, ">5")` Sum the values over 5 in cells A2:A10. Sum if less than < `=SUMIF(A2:A10, "<10", B2:B10)` Sum the values in cells B2:B10 if a corresponding value in column A is less than 10. Sum if equal to = (can be omitted) `=SUMIF(A2:A10, "="&D1)` or `=SUMIF(A2:A10,D1)` Sum the values in cells A2:A10 that are equal to the value in cell D1. Sum if not equal to <> `=SUMIF(A2:A10, "<>"&D1, B2:B10)` Sum the values in cells B2:B10 if a corresponding cell in column A is not equal to the value in cell D1. Sum if greater than or equal to >= `=SUMIF(A2:A10, ">=5")` Sum the values greater than or equal to 5 in the range A2:A10. Sum if less than or equal to <= `=SUMIF(A2:A10, "<=10", B2:B10)` Sum the values in cells B2:B10 if a corresponding value in column A is less than or equal to 10.

### How to use Excel SUMIF with text criteria

Apart from numbers, the SUMIF function enables you to add values depending on whether a corresponding cell in another column contains a given text or not.

Please pay attention that you will need different SUMIF formulas for exact and partial match, as demonstrated in the table below.

 Criteria Formula Example Description Sum if equal to Exact match: `=SUMIF(A2:A8, "bananas", C2:C8)` Sum values in cells C2:C8 if a corresponding cell in column A contains exactly the word "bananas" and no other words or characters. Cells containing "green bananas", "bananas green" or "bananas!" are not included. Partial match: `=SUMIF(A2:A8, "*bananas*", C2:C8)` Sum values in cells C2:C8 if a corresponding cell in column A contains the word "bananas", alone or in combination with any other words. Cells containing "green bananas" or "bananas green" are summed. Sum if not equal to Exact match: `=SUMIF(A2:A8, " <>bananas", C2:C8)` Sum values in cells C2:C8 if a corresponding cell in column A contains any value other than "bananas". If a cell contains "bananas" together with some other words or characters like "yellow bananas" or "bananas yellow", such cells are summed. Partial match: `=SUMIF(A2:A8, " <>*bananas*", C2:C8)` Sum values in cells C2:C8 if a corresponding cell in column A does not contain the word "bananas", alone or in combination with any other words. Cells containing "yellow bananas" or "bananas yellow" are not summed.

And now, let's see the exact "Sum if not equal to" formula in action. As illustrated in the screenshot below, it adds in-stock amounts of all products other than "goldfinger bananas":

`=SUMIF(A2:A7,"<> goldfinger bananas", C2:C7)`

Note. Like most of other Excel functions, SUMIF is case-insensitive, meaning that "<>bananas", "<>Bananas" and "<>BANANAS" will produce exactly the same result.

### Using comparison operators with cell references

If you want to take a step further and get a more universal Excel SUMIF formula, you can replace a numerical or text value in the criteria with a cell reference, like this:
`=SUMIF(A2:A8,"<>"&F1, C2:C8)`

In this case, you won't have to change the formula to conditionally sum values based on another criteria - you will simply type a new value in a referenced cell.

Note. When you use a logical expression with a cell reference, you must use the double quotes ("") to start a text string and ampersand (&) to concatenate and finish the string off, for example "<>"&F1.

The "equal to" operator (=) can be omitted before a cell reference, therefore both of the below formulas are equivalent and correct:

Formula 1: `=SUMIF(A2:A8, "="&F1, C2:C8)`

Formula 2: `=SUMIF(A2:A8, F1, C2:C8)`

### SUMIF formulas with wildcard characters

If you aim to conditionally sum cells based on a "text" criteria and you want to sum by partial match, then you need to use wildcard characters in your SUMIF formula.

The following wildcards are available to us:

• Asterisk (*) - represents any number of characters
• Question mark (?) - represents a single character in a specific position

#### Example 1. Sum values based on partial match

Suppose, you want to sum amounts relating to all sorts of bananas. The following SUMIF formulas will work a treat:

`=SUMIF(A2:A8, "*bananas*", C2:C8)` - the criteria includes text enclosed in asterisks (*).

`=SUMIF(A2:A8, "*"&F1&"*", C2:C8)` - the criteria includes a cell reference enclosed in asterisks, please notice the use of ampersand (&) before and after a cell reference to concatenate a string.

If you want to count only those cells that start or end with certain text, add just one * either before or after the text:

`=SUMIF(A2:A8, "bananas*", C2:C8)` - sum values in C2:C8 if a corresponding cell in column A begins with the word "bananas".

`=SUMIF(A2:A8, "*bananas", C2:C8)` - sum values in C2:C8 if a corresponding cell in column A ends with the word "bananas".

Tip. To use wildcards with cell references, combine them with the help of the concatenation operator (&). In this example, you could also use the following formula to sum all "bananas" in stock: `=SUMIF(A2:A8, "*"&F1&"*", C2:C8)`

#### Example 2. Sum values with a given number of characters

In case you want to sum some values that are exactly 6 letters long, you would use the following formula:

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

#### Example 3. Sum cells corresponding to text values

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

`=SUMIF(A2:A8,"?*", C2:C8)` - adds up values in cells C2:C8 if a corresponding cell in column A contains at least 1 symbol.

`=SUMIF(A2:A8,"*",C2:C8)` - includes seemingly empty cells that contain zero length strings returned by some other formulas, e.g. `=""`.

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

#### Example 4. Treat * or ? as usual characters

If you want either * or ? to be treated as a literal rather than a wildcard, then use the preceding tilde (~). For example, the following SUMIF formula will add values in cells C2:C8 if a cell in column A in the same row contains a question mark:

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

### Sum the largest or smallest numbers in a range

To add the largest or smallest numbers in the range, use the SUM function together with the LARGE or SMALL function, respectively.

#### Example 1. Add a few largest / smallest numbers

If you want to sum just a few numbers, say 5, you can type them directly in the formula, like this:

`=SUM(LARGE(B1:B10,{1,2,3,4,5}))` - sum the largest 5 numbers

`=SUM(SMALL(B1:B10,{1,2,3,4,5}))` - sum the smallest 5 numbers

Note. If there are 2 or more numbers that are tied for last place, only the first will be included. As you see, the second 9 is not added up in the above example.

#### Example 2. Add many top / bottom numbers

If you want to add up many numbers, instead of enumerating them all in the formula, you can nest the ROW and INDIRECT functions in your SUM formula. In the INDIRECT function, use row numbers that represent the number of values you want to add up. For example, the following formulas sum the top and bottom 15 numbers, respectively:

`=SUM(LARGE(B1:B50,ROW(INDIRECT("1:15"))))`

`=SUM(SMALL(B1:B50,ROW(INDIRECT("1:15"))))`

Since these are array formulas, remember to enter them in the array-way by pressing Ctrl + Shift + Enter.

#### Example 3. Sum a variable number of largest / smallest values

If you'd rather not change the formula every time you want to sum a different number of cells, you can put that number in some cell instead of entering it directly in the formula. And then, you can use the ROW and INDIRECT functions as demonstrated above and refer to a cell containing the variable, cell E1 in our case:

`=SUM(LARGE(B1:B50,ROW(INDIRECT("1:"&E1))))` - sums a variable number of top values

`=SUM(SMALL(B1:B50,ROW(INDIRECT("1:"&E1))))` - sums a variable number of bottom values

Remember, these are array formulas, so you have to press Ctrl + Shift + Enter to complete them.

### How to sum cells that correspond to blank cells

If "blank" means cells that contain absolutely nothing - no formula, no zero length string returned by some other Excel function, then use "=" as the criteria, like in the following SUMIF formula:

`=SUMIF(A2:A10,"=",C2:C10)`

If "blank" includes zero length strings (for example, cells with a formula like `=""`), then use "" as the criteria:

`=SUMIF(A2:A10,"",C2:C10)`

Both of the above formulas evaluate cells in column A and if any empty cells are found, the corresponding values from column C are added.

### How to sum cells corresponding to non-blank cells

If you want to sum cells' values in column C when a corresponding cell in column A is not empty, use "<>" as the criteria in your SUMIF formula:

`=SUMIF(A2:A10,"<>",C2:C10)`

The above formula counts values corresponding to all non-empty cells, including zero length strings.

### How to use Excel SUMIF with dates

Generally, you use the SUMIF function to conditionally sum values based on dates in the same way as you use text and numeric criteria.

If you want to sum values corresponding to the dates that are greater than, less than or equal to the date you specify, then use the comparison operators that we've discussed a moment ago. A few Excel SUMIF formula examples follow below:

 Criteria Formula Example Description Sum values based on a certain date. `=SUMIF(B2:B9,"10/29/2014",C2:C9)` Sum values in cells C2:C9 if a corresponding date in column B is 29-Oct-2014. Sum values if a corresponding date is greater than or equal to a given date. `=SUMIF(B2:B9,">=10/29/2014",C2:C9)` Sum values in cells C2:C9 if a corresponding date in column B is greater than or equal to 29-Oct-2014. Sum values if a corresponding date is greater than a date in another cell. `=SUMIF(B2:B9,">"&F1,C2:C9)` Sum values in cells C2:C9 if a corresponding date in column B is greater than the date in F1.

In case you want to sum values based on a current date, then you have to use Excel SUMIF in combination with the TODAY() function as demonstrated below:

 Criteria Formula Example Sum values based on the current date. `=SUMIF(B2:B9, TODAY(), C2:C9)` Sum values corresponding to a prior date, i.e. before today. `=SUMIF(B2:B9, "<"&TODAY(), C2:C9)` Sum values corresponding to a future date, i.e. after today. `=SUMIF(B2:B9, ">"&TODAY(), C2:C9)` Sum values if a date occurs in a week (i.e. today + 7 days). `=SUMIF(B2:B9, "="&TODAY()+7, C2:C9)`

The screenshot below illustrates how you can use the last formula to find the total amount of all products that ship in a week.

#### How to sum values in a given date range

The following question is frequently asked on Excel forums and blogs - "How do I sum between two dates?"

The answer is to use a combination, or more precisely, the difference of 2 SUMIF functions. In Excel 2007 and higher, you can also use the SUMIFS function that allows multiple criteria, which is even a better option. While the latter is the subject of our next article, an example of the SUMIF formula follows below:

`=SUMIF(B2:B9, ">=10/1/2014", C2:C9) - SUMIF(B2:B9, ">=11/1/2014", C2:C9)`

This formula sums up the values in cells C2:C9 if a date in column B is between 1-Oct-2014 and 31-Oct-2014, inclusive.

This formula might seem a bit tricky at first sight, but upon a closer look, it appears quite simple. The first SUMIF function adds up all the cells in C2:C9 where the corresponding cell in column B is greater than or equal to the start date (Oct-1 in this example). Then you just have to subtract any values that fall after the end date (Oct-31), which are returned by the second SUMIF function.

### How to sum values in several columns

To understand the problem better, let's consider the following example. Suppose, you have a summary table of monthly sales. Since it was consolidated from a numbers of regional reposts, there are a few records for the same product:

So, how do you find the total of apples sold in all the states in the past three months?

As you remember, the dimensions of sum_range are determined by the dimensions of the range parameter. That is why you cannot use the formula like `=SUMIF(A2:A9, "apples", C2:E9)` because it will add up the values corresponding to "Apples" in column C only. This is not what we are looking for, right?

The most logical and simplest solution that suggests itself is to create a helper column that calculates individual sub-totals for each row and then reference that column in the sum_range criteria.

Go ahead and place a simple SUM formula in cell F2, then fill down column F: `=SUM(C2:E2)`

After that, you can write a usual SUMIF formula like this:

`=SUMIF(A2:A9, "apples", F2:F9)`
or
`=SUMIF(A2:A9, H1, F2:F9)`

In the above formulas, sum_range is exactly of the same size as range, i.e. 1 column and 8 rows, and therefore they return the correct result:

If you'd rather do without a helper column, then you can write a separate SUMIF formula for each of the columns you want to sum, and then add the returned numbers using the SUM function:

`=SUM(SUMIF(A2:A9,I1,C2:C9), SUMIF(A2:A9,I1,D2:D9), SUMIF(A2:A9,I1,E2:E9))`

Another way is using a more complex array formula (don't forget to press Ctrl + Shift + Enter):
`{=SUM((C2:C9+D2:D9+E2:E9)*(--(A2:A9=I1)))}`

Both of the above formulas will return 2070 in our case.

## Why my SUMIF formula is not working?

There could be several reasons why Excel SUMIF is not working for you. Sometimes, your formula does not return what you expect only because the data type in a cell or in some argument isn't suited for the SUMIF function. So, here is a list of things to check.

#### 1. The range and sum_range parameters should be ranges, not arrays

The first (range) and third (sum_range) parameters of your SUMIF formula must always be ranges. If you try to pass in anything else, for example an array like {1,2,3}, Excel with throw an error message.

Correct formula: `=SUMIF(A1:A3, "flower", C1:C3)`

Wrong formula: `=SUMIF({"flower","tree","bush"}, "flower", C1:C3)`

#### 2. How to sum values from other sheets or workbooks

As almost any other Excel function, SUMIF can reference other sheets and workbooks, provided they are currently open.

For example, the following formula will sum the values in cells F2:F9 in Sheet 1 of Book 1 if a corresponding cell in column A if the same sheet contains "apples":

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

However, this formula won't work as soon as Book 1 is closed. This happens because the ranges referenced by SUMIF formulas in closed workbooks get de-referenced into arrays, and since no arrays are allowed in the range and sum_range arguments, a SUMIF formula will throw a #VALUE! error.

#### 3. To avoid problems, make sure range and sum_range are of the same size

As noted in the beginning of this tutorial, in modern versions of Microsoft Excel, the range and sum_range parameters does not have to be equally sized. In Excel 2000 and older, unequally sized range and sum_range can cause problems. However, even in the most recent versions of Excel 2010 and Excel 2016, complex SUMIF formulas where sum_range has less rows and/or columns than range are capricious. That is why it's considered a good practice to always have the range and sum_range arguments of the same size and shape.

#### 4. How to make your SUMIF formulas work faster

If you've populated your workbook with complex SUMIF formulas that slow down your Excel, check out this article and learn how to make them work faster. The article was written quite long ago, so don't be surprised by the calculation time. Their recommended approaches and formula examples are still actual and brilliant!

The Excel SUMIF examples described in this tutorial only touch on some of the basic usages of this function. In the next article, we'll investigate advanced formulas that harness the real power of SUMIF and SUMIFS and let you sum by multiple criteria. Please stay tuned and thank you for reading!