SUMIF: SUMIF is a function in Excel used to add all the numbers in a range based on one criteria/condition. In case of two or more criteria’s/conditions, the formula used is:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2])
SUMIF Function in Excel can be used in two ways:
- By putting SUMIF Formula in the worksheet cell and entering values in it.
- By using the option of before the formula bar.
Let’s see how we can calculate the sum of a range with two conditions by using two different methods:
According to the given figure, we’re going to calculate total sales of cars for 3 years based on the model of the car and the region where the cars are sold.
- Select cell H6, use SUMIF formula and insert the values in the formula.
(Cell D5:D19 is referred as sum_range, A5:A19 is criteria_range1, F6 is criteria1, C5:C19 is criteria_range2, and G6 is criteria2.)
- Press Enter after entering the values and we’ll get the total sales of Suzuki in the North region.
- By clicking on H6, we can see the SUMIF formula in the formula bar.
- Now lock the formula by pressing F4, but remember not to lock “criteria1” and “criteria2” as they are different for every cell of total sales.
- Click H6 and drag it to H10 to get total sales of all the cars for the mentioned regions.
The steps to get the same result with the same formula are as follows:
- Write =SUMIFS in H6, then click Fx in the toolbar before the formula bar.
- A small window will open in the excel worksheet carrying all the titles of the formula.
- Fill the titles one by one and press OK.
- We’ll get the same values obtained by using method 1.
- Lock the formula and drag it to H10, all the values of total sales will be calculated automatically meeting both the conditions.
- While locking the formula, Select the formula except criteria1 “F6” and criteria2 “G6”
- Otherwise, results won’t be the same.
You can see this is a locked formula in case of the given example.
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.