Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

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.


  • 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


Subscribe to

Get updates on helpful Excel topics

Subscribe to
Trusted by people who work at, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc