Sumif

SUMIF function in Excel: Learn with EXAMPLE

SUMIF is the function that is utilised for the purpose of summing the values in accordance with a solitary criterion. You can determine the total of a set of numbers by using this function and applying a condition while staying inside a given range. This function falls within the category of functions related to mathematics and trigonometry. In the same vein as the name, this will total up if the conditions that were specified are met. The purpose of this function is to calculate the total of a subset of the numbers contained in a huge data set.

How to use the SUMIF function?

SUMIF will calculate the total for the range of cells based on the criteria that you specify. It is necessary to state, inside the formula, the range of cells to which the condition should be applied, the constraint according to which the sum will be calculated, and the range of cells for which the sum must be calculated in order for the condition to be satisfied.

The equation for the MS SUMIF
In general, adhere to a framework in which the criterion, criteria range, and other relevant information must be presented.

SUMIF (range,criteria,sum range)

The range of cells that satisfied the criterion is referred to as the range.
The criterion is the obligatory requirement that must be met.
The range of cells that will be added together if the condition is met is called the sum range.

Example 1: Using SUMIF

Please find attached the data table with the order numbers and the total bill. We need to determine the total amount that has been billed for certain orders.

1) You need to determine the total amount of the bill for a certain brand order whose order number begins with the letters ‘Amzcd.’

S# Order No Bill Amount
1 Amzcd 008 $450
2 Amzcd 009 $500
3 AFJK 010 $1,102
4 Amzcd 011 $850
5 Amzcd 012 $564
6 ABVF 013 $345
7 Amzcd 014 $10.50
8 Amzcd 015 $835.50
9 ABVF 016 $1,236
10 AFJK 017 $478.20
11 Amzcd 018 $100.25

Step 2) Choose the cell that is located below the bill amount, and then use the method to determine the total amount of the bill just for the orders whose first word begins with “Amzcd.”

The formula that was used is as follows:

the expression “=SUMIF (B2: B12, “Amzcd*,” C2: C12)”
The requirements will be checked in the range of cells beginning at position B2 and going all the way to B12.
The string ‘Amzcd*’ is used to determine which conditions are met before the order number is generated.
The range of cells that should be added, according to the criterion, is C2:C12.
Step Four) If you look at the table, the amounts of the bills that have been tallied up according to the criteria specified will be highlighted. The order totals for all of the invoices whose first letter is “Amzcd” will be selected, and the sum will be found.

Step 5: At the conclusion of the column labelled “bill amount,” look for the total amount of the bill to determine whether or not it satisfies the criterion that has been presented.

Example 2: How to use SUMIF with comparison operators

Excel comes with its own collection of comparison operators. In combination with other comparison operators, the SUMIF function can be used. Comparison operators are able to be utilised in order to specify the criteria that are utilised by the SUMIF function. Therefore, it will perform a sum operation if the comparison operation returns a result that is true.

1) You now have the table of data, which includes the product names, the quantity, and the prices. How can you get the total price of the product if the quantity purchased is larger than or equal to one hundred?

S# Product Qty Price
1 Soft drinks 100 $1,789
2 Dress Materials 80 $752
3 Edible Oil 150 $489
4 Nuts 500 $2,589
5 Cookies 205 $1,500
6 Footwear 130 $300
7 Electronic Items 100 $4,500
8 vegetables 410 $3,752
9 Toys 50 $200
10 Furniture 210 $500

Step 2: If you apply the criteria into a logical format such as ‘Qty>=100,’ the SUMIF formula can develop as shown in the following example.

The formula that was used is as follows:

=SUMIF (C2: C11,”>=100″, D2: D11)
C2: C11 is the range of the cell in which the criteria will be looking for ‘>=100’ is the condition that is expressed with the comparison operator greater than and equal to D2: D11 is the range that will be summed in accordance with the criteria C2: C11 is the range of the cell in which the criteria will be looking for C2: C11 is the range of the cell in which the
Step 4: The prices that are acceptable are highlighted in the cells where the quantity is greater than or equal to 100.

Step 5: The total of the values can be found in the field labelled D13; this represents the aggregate of the prices for all of the items whose quantity is either higher than or equal to 100.

Example 3: How to use SUMIF with dates

You are going to learn how to use the SUMIF function with the date by following along with this example. There is also the option of providing the criterion in the form of a date.

1) The table provides a list of various account numbers, billing dates, and payment information. Within the allotted time frame for payment, the sum in question must be deposited into the appropriate account. You can attempt to calculate the total amount that has to be paid for the month of June by utilising the SUMIF function.

S# Account Number Bill Date Payment
1 9160250011660588 1/4/2019 25000
2 9160250011689568 1/28/2019 15000
3 9160250012356588 1/31/2019 8500
4 9160250899560588 6/2/2019 9635
5 9160260231660588 2/10/2019 4582
6 9160250011658588 6/25/2019 10265
7 9160457811660588 3/1/2019 8132
8 9160250011670858 3/12/2019 9562
9 9160250011663569 6/15/2019 7895
10 9160250321660456 6/18/2019 2566

Step 2: Because you already know the date, the criteria may be formed as “>5/31/2019,” which refers to dates that are later in the month of May. At this point, the comparison operator and the date are being used simultaneously with the SUMIF operator.

The formula that was used is as follows:

=SUMIF (“>5/31/2019”, C2: C11, D2: D11)
C2:C11 is the range of cells where the dates are provided, as well as the range of cells where the supplied criteria will be looking for ‘>5/31/2019,’ which is the criteria where the date is greater than ‘5/31/2019.
The range of numbers to add once the condition has been met is from D2 to D11.
Step Four) Listed below are the cell values that fulfil the requirements of the condition that was applied, and the total of these values will be found.

Step 5: The total can be found in the cell D13. And this is the entire amount that will be paid out to a variety of accounts for the month of June.

Summary

The SUMIF function will make the sum of values based on the criteria that are specified.
The SUMIF function may process data in the form of numbers, dates, and even text.
It’s possible for conditions to emerge as a consequence of the actions of other functions or operators.
Only one column at a time can have the SUMIF function applied to it at the same time.
It is permissible to check with a one-of-a-kind condition. The SUMIF function is at #. When the range does not correspond with the criteria range, VALUE will be considered an erroneous result.
A wildcard is going to be used to match up a portion of the word or the sentence. When referring to a single character, use the question mark sign, and use the asterisk symbol when referring to a series of characters.
If the criterion includes both text and numerical values, it should be included in double quotation marks; however, if it solely includes numerical values, it does not need to be enclosed in quotation marks.