Working with the SUMIF Function and Two Conditions

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:

Formula: =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:

Method 1

Figure 1

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.

Formula: =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)

(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.

Method 2

Figure 2

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.

Notes

• While locking the formula, Select the formula except criteria1 “F6” and criteria2 “G6”
• Otherwise, results won’t be the same.

Figure 3

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.