Excel: The SUMIF() Worksheet Function

SUMIF() is a very important Excel worksheet function which can be used to conditionally sum a range of cells. I’ve put together this tutorial to describe some undocumented features of the SUMIF() worksheet function and to provide some useful examples. I've focused the content on the SUMIF() worksheet function itself so a background knowledge on various topics is assumed throughout. Once you've read the thread, if you have any questions then please post your question on the Excel board (http://www.xtremevbtalk.com/forumdisplay.php?f=78) so we can try to help.

An updated version of this tutorial can be found here (http://www.colinlegg.co.uk/Excel/SUMIF.html).


SUMIF() Syntax
Comparison Operator and Wildcard Criteria
Array Criteria
Examples
SUMIF() from VBA
Useful Links



A big thanks to XVBT member kassyopeia (http://www.xtremevbtalk.com/member.php?u=184994) for volunteering great feedback and suggestions while this thread was being constructed.

Firstly, let’s recap and expand on the SUMIF() syntax and remarks in the Excel helpfile:

Syntax

SUMIF(range,criteria,sum_range)

Range is the range of cells you want evaluated.
Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".
Sum_range are the actual cells to sum.


Remarks

The cells in sum_range are summed only if their corresponding cells in range match the criteria.
If sum_range is omitted, the cells in range are summed.
Microsoft Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF function. To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF function.


The argument you pass into the range parameter must be a range reference: if you try to pass an array then Excel’s expression evaluator will reject it and tell you that the formula contains an error:

=SUMIF({1;2;3},1)


However, the criteria parameter will accept an array. The below formula may not seem to give you the result you are hoping for but it can be extremely useful as I will show later:

=SUMIF(A2:A10,{1;2;3})


The sum_range parameter is optional. If you use it, you must pass in a range reference. As noted in the helpfile’s remarks, if it is omitted then the cells in the range parameter are summed. 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 dimensions of the sum_range are actually defined by the range parameter dimensions:

This formula will sum the values in B2:B10 if the corresponding cells in A2:A10 equal "a".

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


This formula will also sum the values in B2:B10 if the corresponding cells in A2:A10 equal "a".

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


This formula will sum the values in B7:B15 if the corresponding cells in A2:A10 equal "a".

=SUMIF(A2:A10,"a",B7)


As already mentioned, 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 values in the referenced ranges become de-referenced and therefore SUMIF() returns an #VALUE! error. The workaround is to use a SUM(IF()) CSE formula [or SUMPRODUCT()], which is shown in the link titled "SUMIF, COUNTIF, COUNTBLANK Return #VALUE! Error" provided in the links section.

The helpfile only touches on some of the expressions we can use in the criteria parameter. I have already mentioned that arrays are acceptable, but how about wildcards and other comparison operators?


Equals

The following formulas are equivalent of each other; equals in the first example is implied.

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


It’s important to note that the condition is not case sensitive. The following are equivalent and will sum for both "a" and "A":

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


It’s often considered good practice to place the "a" constant in a cell and 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(A2:A10,E2,B2:B10)
=SUMIF(A2:A10,"="&E2,B2:B10)


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 E2 is empty then the first formula will sum cells in B2:B10 when corresponding cells in A2:A10 are 0; the second formula will sum cells in B2:B10 when corresponding cells in A2:A10 are empty. So take care with your data types.

Another variation which will only sum cells corresponding to empty cells is this:

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




Does Not Equal

The syntax should be becoming more familiar now. This will sum cells in B2:B10 where the corresponding cells in A2:A10 are not equal to "a":

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


We can also place "a" (without the speech marks) in cell E2 and reference it from there:

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


Or you can even place "<>a" (without the speech marks) in cell E2 and reference it from there:

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


To sum non-empty cells we can use this variation:

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




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

To sum only positive numbers we can use formulas like this:

=SUMIF(B2:B10,">0")
=SUMIF(B2:B10,">"&E2)

Again, take care with your data types. For the second formula to work, E2 must contain a 0.

You can also place ">0" (without the speech marks) and then reference E2:

=SUMIF(B2:B10,E2)



To sum numbers less than or equal to -10 we can use a similar approach:

=SUMIF(B2:B10,"<=-10")
=SUMIF(B2:B10,"<="&E3)




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.

Suppose we want to sum a range corresponding to the surname Smith:

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


Perhaps we want to include cells where "Smith" appears anywhere, for example double-barrel names and to include surnames such as "Smithies":

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


Perhaps we want to sum where the surname is exactly five letters long. Assuming the full name is in each cell, with a space separating the first and last names:

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

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(A2:A10,"* "&REPT("?",F2),B2:B10)


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, eg. ="". Note that both of these ignore non-text types (ie. errors, booleans, numbers) in the range column.

If we want a * or ? to be treated literally we have to precede it with a tilde. This will sum cells which literally correspond to the text "*Smith" (without the speech marks) which may be preceeded by any characters:

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

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 need to sum by multiple 'OR' conditions on the same range then we could use a formula like this:

=SUMIF(A2:A10,"a",B2:B10)+SUMIF(A2:A10,"b",B2:B10)


There is a more concise way of doing this, especially when the number of conditions increases. Instead we can use this:

=SUM(SUMIF(A2:A10,{"a";"b"},B2:B10))


This formula, which uses an inline array constant, does not need to be CSE entered. The SUMIF() function returns two separate amounts in a 2-element vertical array. The amounts correspond to the totals for "a" and "b" respectively. You can see this if you select two cells in a column, type in the below formula and press CTRL+SHIFT+ENTER. The SUM() function simply sums these two results together to give the final answer.

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

Named array constant CRITERIA defined as ={"a";"b"}

=SUM(SUMIF(A2:A10,CRITERIA,B2:B10)) CSE entered
=SUMPRODUCT(SUMIF(A2:A10,CRITERIA,B2:B10))


We also may wish to use a range of cells containing the criteria. Suppose E2 and E3 contain "a" and "b" respectively, we also have to use either of the CSE or the SUMPRODUCT() constructions:

=SUM(SUMIF(A2:A10,E2:E3,B2:B10)) CSE entered
=SUMPRODUCT(SUMIF(A2:A10,E2:E3,B2:B10))


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.

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.

Example 1: How can I only sum cells which correspond to numbers?


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


Under the Excel specifications and limits topic in the Excel helpfile you can see that 9.99999999999999E307 is the largest number allowed to be typed into a cell.


Example 2: How can I sum between 2 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.

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.


Example 3: How can I sum values in column C when column A is equal to "a" and column B is equal to "b" on the same row?

We can't do this using SUMIF() directly on the source data because we can't synchronise the criteria across the two columns. The simple workaround is to use a helper column which will contain boolean values to indicate whether or not both criteria have been met on each row. There are various formulas we can use but the most obvious one utilises the AND() worksheet function. We add this formula to cell D2 and then fill it down column D:

=AND(A2="a",B2="b")

The formula returns TRUE when both conditions are met; otherwise it returns FALSE or an error.

We can now reference column D in the range parameter:

=SUMIF(D2:D10,TRUE,C2:C10)


In Excel 2007 and later we can use the SUMIFS() worksheet function instead.


Example 4: How can I sum for multiple surnames?


=SUM(SUMIF(A2:A10,{"* Smith","* Jones"},B2:B10))

This example demonstrates that we can use wildcards in an array constant.

There are three ways we can be involved with SUMIF() when we are using VBA.


The Range.Formula(), Range.FormulaR1C1() and Range.FormulaArray() Properties

This is a straightforward process. To enter a formula containing SUMIF() we use the Formula or FormulaR1C1 properties:

Sub Example1()

'=SUM(SUMIF(A2:A10,{"a";"b"},B2:B10))
Sheet1.Range("C1").Formula = "=SUM(SUMIF(A2:A10,{""a"";""b""},B2:B10))"

End Sub


If the formula needs to be a CSE formula, we use the Range.FormulaArray property:

Sub Example2()

'{=SUM(SUMIF(A2:A10,E1:E2,B2:B10))}
Sheet1.Range("C1").FormulaArray = "=SUM(SUMIF(A2:A10,E1:E2,B2:B10))"

End Sub

For further information on the Range.FormulaArray property please read the array formula tutorial (http://www.xtremevbtalk.com/showthread.php?t=296012).


The WorksheetFunction Object

If we want to conditionally sum using the SUMIF() worksheet function in VBA, we can access it via the WorksheetFunction object.

Sub Example3()
Dim vResult

'=SUMIF(A2:A10,"* Smith",B2:B10)
vResult = Application.WorksheetFunction.SumIf( _
Arg1:=Sheet1.Range("A2:A10"), _
Arg2:="* Smith", _
Arg3:=Sheet1.Range("B2:B10"))


End Sub

With this method, if SUMIF() returns an error then we have to add an error handler in VBA to handle it. I have included the named arguments in the example so you can see how uninformative they are and the little benefit intellisense gives us. Ideally we would like to see range,criteria and sum_range, but instead we have to work with arg1,arg2 and arg3.

Another way is to call it from the Application object:

Sub Example4()
Dim vResult

'=SUMIF(A2:A10,"* Smith",B2:B10)
vResult = Application.SumIf( _
Sheet1.Range("A2:A10"), _
"* Smith", _
Sheet1.Range("B2:B10"))


End Sub


Using this approach, an error returned by SUMIF() will be held within the vResult variable which we can then handle with the VBA.CVErr() function. For futher information and comparisons between these approaches have a read of this thread (http://www.dailydoseofexcel.com/archives/2004/09/24/the-worksheetfunction-method/) at dailydoseofexcel.

We come back to the earlier point that the 1st and 3rd parameters expect range references. When you are calling SUMIF() via the WorksheetFunction object, do not be tempted to include any additional brackets around the range argument you are passing in:

Sub Example5()
Dim vResult

'type mismatch error
vResult = Application.WorksheetFunction.SumIf( _
(Sheet1.Range("A2:A10")), _
"* Smith", _
Sheet1.Range("B2:B10"))


End Sub

The extra brackets in red cause the range to be de-referenced into an array which, as we have already discussed, is not acceptable.


Application.Evaluate() and Worksheet.Evaluate() Methods

For the more complex SUMIF() formulas, the easiest way to calculate them directly within VBA is to use the Worksheet.Evaluate method. This method assumes that the formula has been CSE entered, so we can treat normal and CSE formulas in the same way.

Sub Example6()
Dim vResult

'=SUM(SUMIF(A2:A10,{"a";"b"},B2:B10))
vResult = Sheet1.Evaluate("=SUM(SUMIF(A2:A10,{""a"";""b""},B2:B10))")

'{=SUM(SUMIF(A2:A10,E1:E2,B2:B10))}
vResult = Sheet1.Evaluate("=SUM(SUMIF(A2:A10,E1:E2,B2:B10))")

End Sub

Again, errors returned by the evaluated expression will be stored within the variant variable. The Evaluate method is qualified by the Sheet1 codename, so all of the unqualified range references within the string expression will be considered to belong to that sheet.

Microsoft Office Online

SUMIF() (http://office.microsoft.com/en-us/excel/HP052092921033.aspx)
Add Numbers (http://office.microsoft.com/en-us/excel/HP030561151033.aspx?pid=CH010005131033)
Summing Up Ways to Add and Count Excel Data (http://office.microsoft.com/en-us/excel/HA011266271033.aspx?pid=CH010005131033)


Microsoft Help and Support

XL: Some Worksheet Functions Do Not Allow Array Constants (http://support.microsoft.com/kb/214286/)
When to use a SUM(IF()) array formula (http://support.microsoft.com/kb/275165)
SUMIF, COUNTIF, COUNTBLANK Return #VALUE! Error (http://support.microsoft.com/kb/260415)
XL2000: SUMIF and COUNTIF Functions Return Incorrect Results When Used with Strings Longer Than 255 Characters (http://support.microsoft.com/kb/231114/en-us)
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 (http://support.microsoft.com/kb/902324/en-us)
COUNTIF/SUMIF Wrong If Cell Has Single Apostrophes (http://support.microsoft.com/kb/150369/en-us)
SUMIF() Function Fails If Sum_Range Contains Links to Text (http://support.microsoft.com/kb/129423/en-us)


Other References

COUNTIF and SUMIF return incorrect values for numbers over 15 characters in length (http://www.dailydoseofexcel.com/archives/2006/10/10/countif-bug/)