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.

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.

Solution examples
amending a formula so that there is another criteria and if it does not meet this criteria then we stick with the original criteria
Solved by A. B. in 40 mins
I am looking for a formula to SUM column L, if Column A matched the relevant date and column C matches the name
Solved by Z. Y. in 16 mins
I am trying to utilize a complicated sumif or sumifs formula that can pull a total amount from a date and a drop down box from another tab.
Solved by E. B. in 15 mins
I have a spreadsheet that totals invoices. A bunch of cells at the bottom of the spreadsheet calculates totals based on which department the invoice is charged to. I'd like to add a function where it also considers the date (so if it's an invoice in January, it goes to a January total, February, etc.). But I can't figure out how to write it properly...
Solved by K. J. in 27 mins
If I want to write a formula that states" "If this name shows up K24, and this word shows up in J24 pull this number from M24" I can creat that formula - however I need to drag this down so it pulls from row 24 - 60 - is there way to do this?
Solved by F. D. in 40 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc