**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*F***x**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.

## Leave a Comment