Use SUMIF Function in Excel to Sum Specific Values

When you need to sum the values that meet a single criterial that you specify, you can use the SUMIF which is built-in function in Microsoft Excel. This function allows you to tell Excel to add up only the values that you want. For example, you have sales from different regions and you want to sum the sales of a specific region, the SUMIF function is what you need. In this lesson will learn the syntax and give some look to the examples. 

Syntax:

SUMIF(range, criteria, [sum_range])

Arguments:

value: (required) The range of cells that will be evaluated by criteria.

criteria: (required) It determines which cells to add. It may be in the form of a number, text, a cell reference, expression or a function. Double quotation marks must be used for text or any criteria which includes logical or mathematical operators. For numeric value, double quotation marks are not needed. In addition, you can use the wildcard characters (?) and (*) in the criteria.  A question mark matches any single character and an asterisk mark matches any sequence of characters.

sum_range: (optional) The actual cells to add together. If sum_range is omitted, the cells in range will be added.

Example 1 - Sum Sale Greater Than 1000

For the example, we will make a list of some sale in excel sheet. Then we will sum only the sale which is greater than one thousand (>1000) by using SUMIF function. Let’s do it.

The formula in cell C17 is:

=SUMIF(C7:C16,''>1000'')

 

You can see that excel sum the sale which is greater than one thousand (>1000). In the above formula I did not apply sum_range argument. I used only two arguments, range and criteria. That’s why, the function summed the values of range argument. This is very simple example, later we will use the sum_range argument too.

Example 2 - Sum Sale Less Than 1000

In the previous example we have summed up the sale which is greater than one thousand (>1000). Now we are going to sum up the sale which is less than one thousand. Let’s see the given below example. 

The formula in cell C17 is:

=SUMIF(C7:C16,"<1000")

In the above two examples, you can see that I used the same formula. There is a little difference of comparison operators in the formula. In the first example I used greater than operator (>) which added up the greater than values. In the second example I used less than operator (<) which added up the less than values.

Example 3 - Sum Sale of 'A' Product

Suppose we have sale of different products in excel sheet. And we need to sum up the sale of a specific product. For this we can apply SUMIF function. Now in this example we are going to use sum_range argument. Let’s see how to apply the function.

The formula in cell C17 is:

=SUMIF(B7:B16,''A'',C7:C16)

We can see that the function summed up only the sale of 'A' product. If you want to sum the sale of 'C 'product, just replace 'A' with 'C'. The function will sum up the sale of 'C' product.

Example 4 - Sum Sale Except A Product

Now in this example we are going to add up the sale except of 'A' product sale.

The formula in cell C17 is:

=SUMIF(B7:B16,''<>A'',C7:C16)

You can see that the SUMIF function added up all the products sale except of 'A' product. In the above formula we used together less than (<) and greater than (>) operators in the criteria argument which simply means not equal. So, the function summed up all the values which is not equal to 'A'. If you want not to sum the sale of 'A' product, just replace 'A' with 'B'. The function will add up all products sale ignoring the 'B' product sale.

Example 5 - Sum Sale of Categorized Products

In this example we will sum up the sale of products which we have categorized. Those products which are not categorized, we will ignore them by using SUMIF function to sum the sale. See the given below screenshot.

The formula in cell C17 is:

=SUMIF(B7:B16,''<>'',C7:C16)

The function added up all the values which are categorized. You may think, how did the function work? Answer is, the function went to all cells in the range to see whether the cells are blank or not. When it reached to those cells which are blank, it ignored them to sum up the sum_range values. On the other hand, when the function reached to those cells which are not blank, it added up sum_range values.

Example 6 - Sum Sale of Uncategorized Products

Now in this example we will add up the sale of uncategorized products. For this we will only write double quotation marks in the criteria. Let’s apply the function.

The formula in cell C17 is:

=SUMIF(B7:B16,'' '',C7:C16)

We can see that the SUMIF function added up all those sales which are not categorized.  In the above formula, I didn’t write anything in the double quotation marks in the criteria. So, the function treated it as blank and wherever the function found blank cells, it added the sum_range values together.  

Example 7 - Sum Sale of Products End with Specific Text

In this example we are going to sum the sale of those products end with specific text. To do this, we will need to use wildcard character (*) in the criteria. Let’s see how to use it?

The formula in cell C17 is:

=SUMIF(B7:B16,''*es'',C7:C16)

The function added up the sale of those products end with ‘es’. How did the function work? You can see in the criteria I wrote asterisk mark * preceding ‘es’. The function went to range cells to find ‘es’. Wherever the function found the ‘es’ in the range, it summed up the sum_range values.

Example 8 - Sum Sale of Products Contain Specific Text

Now we are going to add up the sale of those products contain specific text. In this example we will also use wildcard character (*) in the criteria. Let’s work on it.

The formula in cell C17 is:

=SUMIF(B7:B16,''*p*'',C7:C16)

The function added up the sale of those products contain ‘p’. In the above two examples (7 & 8) you can see I used the same formula. There is a little difference of asterisk mark. In the example number 7 I used single asterisk while in the example number 8 I used double asterisk in the criteria.  Keep in mind that the specific text you specify must come between the asterisk marks as shown above.

Do practice for SUMIF function. If you face any problem during practice, let me know by commenting in the given below comment section. We will solve the problem together.