SUMIF function in Excel: Learn with EXAMPLE

What is SUMIF Function?

SUMIF is the function used to sum the values according to a single criterion. Using this function, you can find the sum of numbers applying a condition within a range. This function comes under Math & Trigonometry functions. Similar to the name, this will sum if the criteria given is satisfied. This function is used to find the sum of particular numbers within a large data set.

In this tutorial, you will learn-

How to use the SUMIF function?

SUMIF will sum the range of cells according to the criteria specified. The range of cells to where the condition should be applied, the constraint according to that the sum will calculate, the range of cell for which the sum to calculate according to the condition need specify within the formula.

How to write a TEST CASE Software Testing Tutorial

Formula of MS SUMIF

Generally, follow a format where the criteria and criteria range, etc. need to be provided.

SUMIF(range,criteria,sum range)

Range: The range of cells which included the criteria
Criteria: The condition that must be satisfied
Sum range: The range of cells to add if the condition is satisfied.

Example 1: Using SUMIF

Given the data table with order numbers and the bill amount. Need to find the sum of the bill amount for particular orders.

Step1) You want to find the sum of the bill amount for a particular brand order where the order number starts with 'Amzcd.'

S#Order NoBill Amount1Amzcd 008$4502Amzcd 009$5003AFJK 010$1,1024Amzcd 011$8505Amzcd 012$5646ABVF 013$3457Amzcd 014$10.508Amzcd 015$835.509ABVF 016$1,23610AFJK 017$478.2011Amzcd 018$100.25

Step 2) Select the cell below to the bill amount and apply the formula to find the sum of bill amount only for the orders, which starts with 'Amzcd.'

Step 3) The formula applied is

'=SUMIF (B2: B12, "Amzcd*," C2: C12)'

B2: B12 is the range of cell where the criteria will be checking.
'Amzcd*' is the criteria applied where the starting of the order number.
C2: C12 is the range of cells to add according to the criteria

Step 4) If you check the table, highlighted are the bill amounts summed according to the criteria given. The bill amount for every order starts with 'Amzcd' are selected, and the sum will find.

Step 5) Check the end of the column bill amount to see the sum of the bill amount, which satisfies the condition given.

Example 2: How to use SUMIF with comparison operators

A set of comparison operators exists in excel. The SUMIF function can use along with comparison operators. Comparison operators can use to specifying the conditions within the SUMIF function. So if the comparison operation returns a true value, it will sum up the values.

Step 1) You have the table of data with name products, Qty, and price. How can you find the total price for the product for which the Qty is greater than and equal to 100?

S#ProductQtyPrice1Soft drinks100$1,7892Dress Materials80$7523Edible Oil150$4894Nuts500$2,5895Cookies205$1,5006Footwear130$3007Electronic Items100$4,5008vegetables410$3,7529Toys50$20010Furniture210$500

Step 2) If you apply the criteria into logical format 'Qty>=100', and the SUMIF formula can form as below.

Step 3) The formula applied is

=SUMIF (C2: C11,">=100", D2: D11)

C2: C11 is the range of cell where the criteria will be looking for
'>=100' is the condition which expressed with comparison operator greater than and equal to
D2: D11 is the range to be summed according to the criteria

Step 4) Highlighted cells are the prices which satisfy the criteria, where the Qty>=100

Step 5) The sum of values are in the cell D13; it is the sum of prices for the products which have Qty greater than or equal to 100.

Example 3: How to use SUMIF with dates

In this example, you will learn how to use SUMIF function with the date. The criteria can also give in the form of date.

Step 1) The table shows different account numbers, bill dates, and payment. The amount needs to be paid to the corresponding account within the bill date given. By using the SUMIF, you can try to find the sum of the amount to be paid for June.

S#Account NumberBill DatePayment191602500116605881/4/201925000291602500116895681/28/201915000391602500123565881/31/20198500491602508995605886/2/20199635591602602316605882/10/20194582691602500116585886/25/201910265791604578116605883/1/20198132891602500116708583/12/20199562991602500116635696/15/201978951091602503216604566/18/20192566

Step 2) Since the date is with you, the criteria can form as '>5/31/2019' which refer to the dates after May. Here the comparison operator and date are using at a time with SUMIF.

Step 3) The formula applied is

=SUMIF (C2: C11,">5/31/2019", D2: D11)

C2: C11 is the range of cells where the dates are given and are the range where given criteria will be looking for
'>5/31/2019' is the criteria where date greater than '5/31/2019'
D2: D11 range of numbers to add once the condition is satisfied

Step 4) Below are the cell values which satisfies the applied condition, and the sum of these values will find.

Step 5) The sum is there in D13. And is the total amount to be paid to multiple accounts for June.

Download the above Excel Data File

Summary

SUMIF function will make the sum of values according to criteria given.
SUMIF function is compatible with numeric, date, text data
Conditions can express from the result of other functions or operators
SUMIF function can be applied only for a single column at a time
A unique condition is allowed to check with SUMIF function
#VALUE will be an error result when the range does not match with criteria range
Part of the word or sentence will be matched using a wildcard. Asterisks symbol use with a series of characters and question mark for a single character.
While using text along with numeric values, the criteria should enclose within a double quotation, but not if it consists only of a numeric value.

ORIGINAL Article