Sum values based on multiple conditions

Consider that you have to sum values with multiple conditions, like the sum of product sales in a certain region. This is a convenient time for using the SUMIFS function in a formula.

Applies to: Excel for Microsoft 365, Excel for the web, Excel 2019, Excel 2016, Excel 2013.

Check out this example in which we find ourselves with two conditions: we want the sum of Meat sales (from column C) in the South region (from column A).

Data in an Excel worksheet

Here’s a formula you can use to achieve this:

=SUMIFS(D2:D11,A2:A11,”South”,C2:C11,”Meat”)

As a result, you get the value 14,719.

Let’s inspect more closely at each element of the formula.

Breaking SUMIFS down – Step 1

=SUMIFS is an arithmetic formula. It calculates numbers, which in this case are in column D. Firstly, the first step is to clarify the location of the numbers:

=SUMIFS(D2:D11,

In other words, you intend to have the formula sum numbers in that column if they satisfy the conditions. That cell range is the first argument in this formula—the first aspect of data that the function must have as input.

Step 2

Secondly, you are looking to locate data that meets two conditions, so you type your first condition by defining the data’s location for function (A2:A11) and also what the condition is—which is “South”. Most importantly, be aware of the commas between the separate arguments:

=SUMIFS(D2:D11,A2:A11,”South”,

Quotation marks around “South” indicate that this is text data.

Step 3

Finally, you state the arguments for your second condition – the range of cells (C2:C11) that includes the word “meat,” alongside the word itself (surrounded by quotes) so that Excel can match it. End the formula with a closing parenthesis ). Then, press Enter. The result, once more, is 14,719.

=SUMIFS(D2:D11,A2:A11,”South”,C2:C11,”Meat”)

As you enter the SUMIFS function in Excel, if you forget the arguments, help is right at your disposal. After you enter =SUMIFS(, Formula AutoComplete displays below the formula, with the list of arguments in their correct order.

Seeing the image of Formula AutoComplete and the list of arguments, in our example sum_range is D2:D11, the column of numbers you plan to sum; criteria_range1 is A2.A11, the column of data where criteria1 “South” is found.

Using Formula AutoComplete to enter the SUMIFS function to sum values with multiple conditions.

As you type, the remainder of the arguments will emerge in Formula AutoComplete (not illustrated here); criteria_range2 is C2:C11, the column of data where criteria2 “Meat” situates.

If you select SUMIFS in Formula AutoComplete, an article launches to provide you more info.

Give it a try

If you want to play around with the SUMIFS function, here’s some sample data and a formula that applies the function.

You can work with sample data and formulas directly here, in this Excel for the Web workbook. Edit values and formulas, or insert your own values and formulas and watch the results change in real-time.

Copy all the cells in the table underneath, and paste into cell A1 in a new worksheet in Excel. You might want to extend column widths to have a better view of formulas.

RegionSalespersonTypeSales
SouthItoBeverages3571
WestLanninDairy3338
EastMakovecBeverages5122
NorthMakovecDairy6239
SouthJordanProduce8677
SouthLanninMeat450
SouthLanninMeat7673
EastMakovecProduce664
NorthLanninProduce1500
SouthJordanMeat6596
FormulaDescriptionResult
‘=SUMIFS(D2:D11,A2:A11,
“South”, C2:C11,”Meat”)
Sums the Meat Sales in
Column C in the South
region in Column A (result is 14719).
=SUMIFS(D2:D11,A2:A11,
“South”, C2:C11,”Meat”)

Notes: 

  • Want further examples to sum values with multiple conditions? You’ll find more in the SUMIFS function article.
  • If you want to produce a total value for only one range based on a value in another range, use the SUMIF function.

Expand your Office skills

EXPLORE TRAINING

Got It

Get instant Excel help

CONNECT TO AN EXPERT NOW 

Subject to Got It terms and conditions

Leave a Reply

%d bloggers like this: