SUMIF Formulas

1 Introduction

At some point in your Excel work you are almost certainly going to want to conditionally sum a range of cells. If you find yourself in that situation then SUMIF() is probably going to be what you need: it’s a very important Excel worksheet function which can be used to sum a range of cells based upon a given criterion. The great news is that, once you’ve invested some time in understanding SUMIF(), it will take you very little effort to get up to speed with the other “IF” worksheet functions: COUNTIF(), COUNTIFS(), AVERAGEIF(), AVERAGEIFS() and SUMIFS(). This post is an updated version of a knowledge base article I wrote a few years ago for an online forum.

2 Syntax

SUMIF() has three parameters which appear in this order:

SUMIF(range, criteria, [sum_range])

• Range is the area of the worksheet where you want to check if a condition has been met.
• Criteria is the condition that must be met within range.
• If the criteria is met in  range then the corresponding cells in sum_range will be summed.

In the example below we have a simple worksheet to illustrate the syntax. Column B contains a list of names and column D has a list of corresponding amounts. We want to determine the sum of all amounts relating to Smith so we add a criterion into a spare cell and construct a SUMIF() formula in cell G2. Each cell in column B is checked to see if it contains the word Smith and, if it does, the corresponding cell from column D is included in the sum. The result is 1 + 4 = 5.

 

Before we look at some more examples, let’s go through some important rules and gotchas which you need to be aware of. In particular, let’s have a look at the values (arguments) which can be passed into each parameter.

3 Range and Sum_Range

3.1 Range References Only!

The argument you pass into the range parameter must be a range reference: if you try to pass in anything else then Excel’s expression evaluator will reject it and tell you that the formula contains an error. In this screenshot I have attempted to pass an array constant into it:

The sum_range parameter is optional. If you use it, you must also pass in a range reference. As noted in the help file’s remarks, if it is omitted then the cells in the range parameter are summed.

As we have discussed above, you cannot pass an array into the first and third parameters of the SUMIF() worksheet function. One implication of this is that we cannot use SUMIF() on closed workbooks. When the referenced workbook is closed, the ranges within that workbook which we have referred to in our formula become de-referenced into arrays and therefore SUMIF() returns an #VALUE! error. The workaround is to use an array or SUMPRODUCT() formula, which is shown in the link titled “SUMIF, COUNTIF, COUNTBLANK Return #VALUE! Error” provided in the links section at the end.

3.2 Dimensions of Sum_Range

There is an interesting point to be made regarding the dimensions of the range and sum_range parameters: if you include a sum_range range, it only ever defines the top left cell of the range to be summed. The remaining area of the sum_range is actually defined by the dimensions of the range argument. In the below example only D2 has been specified in the sum_range parameter but SUMIF() looks at the dimensions of range – 1 column x 6 rows – and therefore considers the sum_range to be D2:D7, which is 1 column x 6 rows starting from D2. The total is still 5.

  

However, in earlier versions of Excel having range and sum_range unequally sized can cause problems: there is a link in section 6.2 called “XL2000: Formula Using SUMIF() Worksheet Function Does Not Recalculate Sum” which describes the issue.

In more recent versions of Excel (post XL 2000) this problem has been fixed, but the trade-off is that SUMIF() formulas where sum_range has less rows or columns than range are volatile. Therefore I recommend that it is good practice to make the range and sum_range equal sizes.

4 Criteria

So far we have covered the range and sum_range parameters but we haven’t had a proper look at the criteria parameter. The help file only touches on some of the expressions we can use.

4.1 Criteria Data Types

 We can pass in many different data types or references including:

• References to ranges or names
• Strings
• Numbers
• Logical values
• Error values
• Arrays

Usage of these data types except for error values is covered in the examples in the remainder of this article. Section 4.4 specifically addresses arrays.

We can also build up expressions which include comparison operators and wildcards. Firstly, let’s have a look at the comparison operators.

4.2 Comparison Operators

• =        Equal to
• <>      Not Equal To
• >        Greater Than
• >=      Greater Than Or Equal To
• <        Less Than
• <=      Less Than Or Equal To

4.2.1 Equal To

In the examples we’ve seen so far we have implicitly used the equals operator. The equal to operator should be used when you want to conditionally sum where range equals a particular value. The following formulas are equivalent of each other; equals in the first example is implied.

=SUMIF(B2:B7,"Smith",D2:D7)

=SUMIF(B2:B7,"=Smith",D2:D7)

It’s important to note that the condition is not case sensitive. The following are equivalent:

=SUMIF(B2:B7,"Smith",D2:D7)

=SUMIF(B2:B7,"SMITH",D2:D7)

It’s often considered good practice to place the criteria argument in a cell and to reference it from there. To replicate the first form above we can reference the cell directly; to reference the second form above we can use the concatenation operator:

=SUMIF(B2:B7,F2,D2:D7)

=SUMIF(B2:B7,"="&F2,D2:D7)

The first of these two formulas is the one we used in the syntax section.

It is important to note that whilst these two formulas will often produce the same results, they are actually different due to the string data type coercion in the second formula. If F2 is empty then the first formula will sum cells in D2:D7 when corresponding cells in B2:B7 are 0; the second formula will sum cells in D2:D7 when corresponding cells in B2:B7 are empty.

4.2.2 Not Equal To

The not equal to operator should be used when you want to conditionally sum where range does not equal a particular value. Let’s sum the amount paid for names which are not equal to Smith:

=SUMIF(B2:B7,"<>Smith",D2:D7)

We can also place Smith in a cell and reference it from there:

  

The answer is -2 + -3 + 5 + 6 = 6.

A third way would be to place <>Smith in cell F2 and reference it from there:

=SUMIF(B2:B7,F2,D2:D7)

4.2.3 Greater Than / Greater Than Or Equal To / Less Than / Less Than Or Equal To

These operators are typically used for number comparisons. To sum only positive numbers from our amount column we can use formulas like this:

=SUMIF(D2:D7,">0")

=SUMIF(D2:D7,">"&F2)

Again, take care with your data types. For the second formula to work, F2 must contain a 0 (don’t leave it empty).

The answer is 1 + 4 + 5 + 6 = 16

You can also place >0 in F2 and then reference F2:

=SUMIF(D2:D7,F2)

These operators can also perform non-case sensitive text comparisons. Let’s get a total amount for all surnames beginning with “s” or later:

The answer is 1 + -2 + 4 + 5 + 6 = 14

Now let’s have a look at some wildcard tokens.

4.3 Wildcards

The wildcards we have available to us are:

• *     represents any number of characters
• ?      represents a single character
• ~     means that the next character should be treated as a literal

The wildcards are only applicable when range contains text.

Perhaps we want to sum amounts where Smith appears anywhere (for example in double-barrel names) and to include names such as Smithies:

=SUMIF(B2:B7,"*Smith*",D2:D7)
=SUMIF(B2:B7,"*"&F2&"*",D2:D7)

The answer is 1 + 4 + 5 + 6 = 16.

Perhaps we want to sum where the name is exactly five letters long:

=SUMIF(B2:B7,"?????",D2:D7)

The REPT() worksheet function is very useful in this situation because it allows us to dynamically control the number of ? repeated wildcards in the criteria expression:

  

=SUMIF(B2:B7,REPT("?",F2),D2:D7)

The answer is 1 + -3 + 4 = 2.

The ~ wildcard should be used in situations where you want either the *, ? or ~ wildcards to be treated as literals rather than as wildcards. So, to have * treated as a literal you would use "~*".

The next sub-section looks at the more advanced topic of passing array arguments into the criteria parameter.

4.4 Using Arrays In the Criteria Parameter

SUMIF() is generally considered to only be capable of summing by one criterion. This is true but, in certain circumstances, we can use an array in the criteria parameter to expand the capability.

If we want to sum the amounts where the name is Smith OR Williams then we could use a formula like this:

=SUMIF(B2:B7,"Smith",D2:D7)+SUMIF(B2:B7,"Williams",D2:D7)

However there is another way of doing this which is more concise when the number of OR conditions increases. Instead we can use this:

=SUM(SUMIF(B2:B7,{"Smith";"Williams"},D2:D7))

This formula, which uses an inline, vertical array constant, does not need to be CSE entered.

When an array is passed into criteria, SUMIF() has been registered to return an array as an answer. The returned array will have the same dimensions as the criteria array. In this case we passed in a 2 element vertical array so the result returned by SUMIF() will also be a 2 element vertical array. The first element in the result is the sum corresponding to Smith; the second element is the sum corresponding to Williams. We want the sum of the result array so we simply wrap the SUMIF() function with the SUM() function.

SUMIF() returns an answer of {5;-2}. The 5 is the sum for Smith; the -2 is the sum for Williams. SUM() adds these together: 5 + -2 = 3.

We require specific circumstances to be able to condense the formula down in this manner:

• The range and sum_range arguments must be the same for all criteria.
• The criteria must be mutually exclusive: this is very important because, if they are not, then any overlap will result in the same value being summed multiple times. It may be possible to add an adjustment to the formula to allow for values which meet multiple criteria, but the adjustment would have to be carefully designed to ensure accurate results.

Of course, we may not always want to use an inline array constant. If we want to use a named array constant, then either the formula has to be CSE entered or we have to use SUMPRODUCT() instead of SUM():

With a named array constant, CRITERIA, defined as ={"Smith";"Williams"}

=SUM(SUMIF(B2:B7,CRITERIA,D2:D7))                CSE Entered

or

=SUMPRODUCT(SUMIF(B2:B7,CRITERIA,D2:D7))

We also may wish to use a range of cells containing the criteria. Suppose F2 and F3 contain Smith and Williams respectively, we also have to use either of the CSE or the SUMPRODUCT() constructions:

=SUM(SUMIF(B2:B7,F2:F3,D2:D7))                       CSE Entered
=SUMPRODUCT(SUMIF(B2:B7,F2:F3,D2:D7))

The reason we have to use these constructions for named array constants and ranges is that we have to   explicitly de-reference them to arrays. In these examples I have passed vertical array/range arguments into the criteria parameter but please note that horizontal or 2-D arguments are equally viable.

Note that we can also use wildcards within the array.

There are other SUMPRODUCT() formula constructions which do not use SUMIF() which can calculate this result. However, testing shows that this approach is more efficient than other SUMPRODUCT() variations.

We have now covered all of the main variations. In the following section we will look at some SUMIF() frequently asked questions.

5 Frequently Asked Questions

• How Can I Sum Cells Which Correspond To Empty Cells?

With “Empty” meaning that the cells contains absolutely nothing – no formula, no zero length string, etc – then use:

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

If you want to include zero length strings (for example, if the cell has a formula such as ="") then use:

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

• How Can I Sum Cells Which Correspond To Non-Empty Cells?

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

• How Can I Sum Cells Which Contain Error Values?

=SUMIF(A2:A10,"<="&9.9999999999999E307)

To maintain international compatibility (the . decimal separator may be an issue) the big number is concatenated with the <= operator. Under the Excel specifications and limits topic in the Excel help file you can see that 9.99999999999999E307 is the largest number allowed to be typed into a cell. A side effect of concatenating the number means that we have to drop the last digit in our formula. These two alternatives are also tidy:

=SUM(SUMIF(A2:A10,{"<0",">0"}))

=SUMIF(A2:A10,"<0")+SUMIF(A2:A10,">0")

Finally, another way of doing this has become available in Excel 2010 and later:

=AGGREGATE(9,6,A2:A10)

Where 9 stands for SUM() and 6 stands for ignore errors.

• How Can I Sum Cells Which Correspond To Text?

If you have a range containing a mixture of data types and you only want to sum cells containing text, then these two variations could be useful:

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

=SUMIF(A2:A10,"?*",B2:B10)

The difference between them is that the first one will sum corresponding cells in column B where there is a formula which returns an empty string in column A, e.g. ="". Note that both of these ignore non-text types (ie. errors, booleans, numbers) in range.

• How Can I Sum Between Two Dates?

With the start and end dates to be referenced in cells E2 and E3, we can use:

=SUMIF(A2:A10,">="&E2,B2:B10)-SUMIF(A2:A10,">"&E3,B2:B10)

This formula is a little tricky to come up with, but once we have it in front of us we can see that it is quite simple. The first SUMIF() adds up all the cells in B2:B10 where the corresponding cells in A2:A10 are greater than or equal to the begin date. Then we just have to subtract any numbers which fall after the end date, which we do with the second SUMIF().

In Excel 2007 and later we can use SUMIFS() which is a better option.

=SUMIFS(B2:B10,A2:A10,">="&E2,A2:A10,"<="&E3)

Another way of calculating this with a formula is to use SUMPRODUCT(), but this is not as good because SUMPRODUCT() is more resource hungry than two SUMIF() functions, so the formula will be slower to calculate.

=SUMPRODUCT(--(A2:A10>=E2),--(A2:A10<=E3),B2:B10)

• How Can I Sum Values Based On Two Conditions?

It depends on what the two conditions are but usually when this question is asked the requirement is to sum only when two separate columns are both equal to certain values.

We can’t do this using SUMIF() directly on the source data because we can’t synchronise the criteria across the two columns. A workaround is to use a helper column which will indicate whether or not both criteria have been met on each row. Here’s an example where we only want the sum of Amount when both the First Name is Michael and the Surname is Smith:

There are various formulas we can use in the helper column. In this example we added this formula into cell F2 and then copied it down to row 7:

=AND(B2=H$2,C2=I$2)

When both conditions are met the formula returns TRUE, otherwise it returns FALSE. If there was an error value in B2:C7 then it would return an error value so in that situation we would have to use a slightly different helper column formula.

We then just sum the amounts where the helper column is TRUE using the SUMIF() formula in cell J2.

Again, there are numerous alternatives to using SUMIF() to do this. In Excel 2007 and later the SUMIFS() worksheet function is a better alternative which does not require a helper column. DSUM(), SUMPRODUCT(), SUM() CSE formulas and pivot tables are also frequently used.

• How Can I Sum A Range Which Has Different Dimensions To The Criteria Range?

Section 3.2 explained that the dimensions of sum_range are determined by the dimensions of range. This means that with SUMIF() the most practical way to achieve this task is almost always by using a helper column for the sum_range which has the same dimensions as range.

In the example below each person has three amounts and we want to find the total amount for anyone with the name Smith. We use a helper column to create individual totals (which is probably useful information in itself) and then we reference that column from the SUMIF() formula.

The formula placed in G2 and filled down the G column is a simple SUM() formula:

=SUM(D2:F2)

Again, there are alternatives such as SUMPRODUCT() or a pivot table.

• How Can I Calculate The Absolute Sum?

=SUMIF(A2:A10,">0") - SUMIF(A2:A10,"<0 )

This formula is explained in the following blog post:

https://colinlegg.wordpress.com/2013/06/03/calculate-the-absolute-sum-in-excel/

 

6 Links, Bugs and Further Reading

6.1 Microsoft Office Online

• SUMIF()
• Add Numbers
• Summing Up Ways to Add and Count Excel Data

 6.2 Microsoft Help and Support

• XL: Some Worksheet Functions Do Not Allow Array Constants
• When to use a SUM(IF()) array formula
• SUMIF(), COUNTIF(), COUNTBLANK() Return #VALUE! Error
• XL2000: SUMIF() and COUNTIF() Functions Return Incorrect Results When Used with Strings Longer Than 255 Characters
• The recalculation of a worksheet that contains a SUMIF() formula takes longer to finish than expected in Excel 2007, in Excel 2003, and in Excel 2002
• COUNTIF()/SUMIF() Wrong If Cell Has Single Apostrophes
• SUMIF() Function Fails If Sum_Range Contains Links to Text
• XL2000: Formula Using SUMIF() Worksheet Function Does Not Recalculate Sum

7 Acknowledgements

• http://www.xtremevbtalk.com member kassyopeia for some great ideas and suggestions, particularly on array criteria (section 4.4).
• Luke Wisbey for pointing out that SUMIF() can be volatile (section 3.2).