Excel SUMIF function is one of the most useful and interesting formula in MS Excel. It is used to calculate the summation of a range based on some criteria. In this tutorial, we are going to take in-depth look at SUMIF formula with easy to follow examples.
I assume here that you know the excel basics and can perform simple operations like sum, average, saving the workbook, etc. You may consider SUMIF function as the combination of SUM and IF function. But before we start our tutorial, here is the quick round up of SUMIF for those who just want to have a quick look at it.
SUMIF(range, criteria, [sum_range])
SUMIF(look into this range, which meets this criteria, [do sum operation in this range])
Here you are providing the list (range) from which you want to select the values meeting the condition (criteria) specified and add the selected values from the same list or other list (sum_range).
=SUMIF(A1:A20, “>10”, B1:B20)
Sum the cells in range B1:B20 if the value in corresponding A1:A20 range is greater than 10.
Sum the cells in range A1:A20 which are greater than 10.
So, this was a quick round-up. Let’s move on to the detailed tutorial.
Excel SUMIF function
As the name suggests, SUMIF is the combination of two widely used excel functions – SUM and IF. You use SUMIF formula to find the Sum of values which meets a condition specified by you. This formula becomes handy in a situation where you have a large dataset and you are suddenly asked to get the sum of only selected items.
Let’s use an example for easy understanding. Suppose a company sells products “A” and “B” in “East” and “West” zones. The table shows the number of sales for years 2009, 2010 and 2011 divided in zone and product wise.
Now, if you are asked to get the number of sales in east zone, what will you do? If you are not using SUMIF formula, you will likely to sort the data by zone and then use the SUM function to add the sales number for east zone. If the data is too large, it will be time consuming and you are doing extra operations of sorting the data table. Here is the SUMIF function for your rescue!
With SUMIF, you can quickly find the sum of values for east zone which is 256.
As I already mentioned, the “sum_range” is optional in syntax. If you want to add the cells in the specified in range itself, you should leave this argument blank.
If, in the example above, we are required to get the SUM of sales figures which are less than 40, we can use the following formula.
And as a result, you get 159. Simple, isn’t it?
SUMIF Function Arguments
- range – Required argument. Here you specify the range of cells which you want to evaluate based on some condition. Each of the cells in this range must be a number, name, array or reference containing numbers. Blank cells are ignored.
- criteria – Required argument. The condition specified by you. This can be a number, reference, text or function. Examples of correct criteria are 10, “>10”, A5, “East”, DAY(Now()), etc. As you can see here, except from numeric values and reference, you need to enclose the criteria in double quotation marks.
You can also use wild card characters – question mark (?) or asterisk (*) in criteria. As you know, a question mark will match any single character while an asterisk will match any sequence of characters.
- sum-range – Optional argument. The range of cells you want to add. You put this range only if the range you want to add is different from the range of values you want to evaluate. Here sizes of sum-range and range arguments do not necessarily be the same.
If you want to copy the formula, the cell reference will get changed. In that condition, it is advisable to use absolute references. For creating absolute reference, select the formula in the formula bar and press F4 button. This will convert your formula to absolute reference like the one below and you can copy this to any cell in the worksheet.
If you don’t want to use F4, add the dollar ($) sign before the character and digit of the cells name manually (like $D$2 for cell D2).
Related Excel Formula – COUNTIF & AVERAGEIF
Similar to SUMIF, there is also a COUNTIF function available in excel. As you can guess, this function works the same way as SUMIF. COUNTIF will count the number of cells which match the criteria defined by you. Now that you already know about SUMIF function, you can use the COUNTIF function quite easily. And if you are using Excel 2007 or above, you also have the AVERAGEIF function which finds the average value of the cells which meet the condition.