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 withinrange
. If the
criteria
is met inrange
then the corresponding cells insum_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 dereferenced 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 tradeoff 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 noncase 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 doublebarrel 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 subsection 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
andsum_range
arguments must be the same for allcriteria
.  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 multiplecriteria
, 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 dereference them to arrays. In these examples I have passed vertical array/range arguments into the criteria
parameter but please note that horizontal or 2D 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 NonEmpty 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 nontext 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/calculatetheabsolutesuminexcel/
6 Links, Bugs and Further Reading
6.1 Microsoft Office Online
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).
Pingback: SUMIF From VBA  RAD Excel
Pingback: countif on column A * count in Column B  dBforums
Pingback: COUNTIF Formula
Pingback: Countifs Question
Pingback: If Statement false check next row for true condition  Page 2
Pingback: SumIF on varying columns for months
How can I include a vlookup in the SUMIF function ?
LikeLike
Hi Marco. Please would you describe in more detail what you need to do?
LikeLike
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 !
LikeLike
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 !
LikeLike
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 toB/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 toB/W()
.LikeLike
Thank you Colin ! I will work on that !
LikeLike