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

 6.2 Microsoft Help and Support

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).

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel and tagged , , , , , , , , , , , , , , , , , , , . Bookmark the permalink.

12 Responses to SUMIF Formulas

  1. Pingback: SUMIF From VBA | RAD Excel

  2. Pingback: countif on column A * count in Column B - dBforums

  3. Pingback: COUNTIF Formula

  4. Pingback: Countifs Question

  5. Pingback: If Statement false check next row for true condition - Page 2

  6. Pingback: SumIF on varying columns for months

  7. Marco says:

    How can I include a vlookup in the SUMIF function ?

    Like

    • Colin Legg says:

      Hi Marco. Please would you describe in more detail what you need to do?

      Like

      • Marco says:

        Hi Colin,
        Thank you, I have in one worksheet the following information:

        A B C
        DCPP (63397)MAWER MEMBER -928.87 0 0
        DCPP (63397)MAWER EMPLOYER 0 500 -1000
        DCPP (63397)MAWER Total -928.87 500 -1000

        In other worksheet I need to summ al the positive amounts and in another all the negative amounts life follows:
        B/W (+) B/W (-)
        DCPP (63397)MAWER Total 500 1,928.87

        So the information comes from the first table, and need to be reflect on the second table, adding in one column the positive amounts and in another column the negative amounts. Columns of the first and second tables are always the same, the rows could change maybe one month starting in row 10 and next month in row 15, there are much more rows but with one example I can reply for all the worksheet, that is the reason I think I need to use Vlooup and sumif, I have tried with different arragment in the formula but I can’t find the right one.

        Hope I explain what I need to do

        Thanks a lot !

        Like

      • Marco says:

        I can’t paste an image of the worksheet, hope the following tables are more clear:

        Table 1:
        A B C
        DCPP (63397)MAWER MEMBER -928.87 0 0
        DCPP (63397)MAWER EMPLOYER 0 500 -1000
        DCPP (63397)MAWER Total -928.87 500 -1000

        Table 2:

        B/W (+) B/W (-)
        DCPP (63397)MAWER Total 500 1,928.87

        Many thanks !

        Like

      • Colin Legg says:

        Hi Marco,

        It’s not ideal that you have the totals within Table1 but I think we can work with it. I can go through a formula approach with you if you want, but the first option I would look at is to use a pivot table. To do this your Table1 will need to have column headers, so I’ll assume your data is set up in a table called Table1 as follows:

        Col1 | Col2 | Col3 | Col4 | Col5 |
        DCPP (63397)MAWER | MEMBER | -928.87 | 0 | 0 |
        DCPP (63397)MAWER | EMPLOYER | 0 | 500 | -1000 |
        DCPP (63397)MAWER | Total | -928.87 | 500 | -1000 |
        DCPP (63398)MAWER | MEMBER | 50 | -10 | -800 |
        DCPP (63398)MAWER | EMPLOYER | 80 | 30 |0
        DCPP (63398)MAWER | Total | 130 | -20| -800|
        DCPP (63399)MAWER | MEMBER | 20 | 0| -800 |
        DCPP (63399)MAWER | EMPLOYER 80 | -800 0 |
        DCPP (63399)MAWER | Total | 100 | -800| -800 |

        Insert a pivot table which references Table1. When I did this in Excel 2007 the source data reference looked like this:
        Table1[#All]

        An advantage of having your data in a table and referencing it this way is that when the table grows, the pivot table will automatically reference the new data; all you have to do is refresh the pivot table.

        Add Col2 to the Report Filter section and filter it by Total.
        Add Col1 to the Row Labels section.

        Now it’s time to create some calculated fields.
        Select any cell on the pivot table, choose the Options tab on the ribbon and click on Formulas | Calculated Field.

        In the name box type in CalcB/W(+)
        In the formula box type in:
        = (Col3>0) * Col3 + (Col4>0) * Col4 + (Col5>0) * Col5
        Click on Add

        In the name box type in CalcB/W(-)
        In the formula box type in:
        = - ( (Col3<0) * Col3 + (Col4<0) * Col4 + (Col5<0) * Col5 )
        Click on Add

        Click on OK and return to the pivot table.

        Add CalcB/W(+) from the Field List to the Values section of the pivot table. Select it in the pivot table and click on Field Settings on the ribbon and rename it to B/W(+).

        Add CalcB/W(-) from the Field List to the Values section of the pivot table. Select it in the pivot table and click on Field Settings on the ribbon and rename it to B/W(-).

        Like

      • Marco says:

        Thank you Colin ! I will work on that !

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s