SUMIF function in Excel: Learn with EXAMPLE
Susan Gipson
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.
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.
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 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) 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# | 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 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 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) 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.