Excel
Sparklines in Excel: What is, How to Use, Types & Examples
What is Sparklines in Excel? Sparkline in Excel is a small graph which is used to represent a series...
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-
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.
Generally, follow a format where the criteria and criteria range, etc. need to be provided.
SUMIF(range,criteria,sum range)
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)'
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.
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)
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.
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)
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
What is Sparklines in Excel? Sparkline in Excel is a small graph which is used to represent a series...
What is ISBLANK function? ISBLANK function used to check whether a cell is empty or not. Since this...
Excel and CSV both help store data in tabular format. Besides this commonality, there are tons of...
Download PDF Following are the Most Frequently Asked Excel Formulas in an interview 1) SUM...
Microsoft Excel is a spreadsheet program that is used to record and analyze numerical data. Excel...
In this Microsoft Excel tutorial, we will learn the Microsoft Exel basics. These Microsoft Excel...