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

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

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.

## 0 comments:

## Post a Comment