Excel SUMIFS: Only sum values ​​that meet multiple criteria

The SUM function extends the utility of the SUMIF function by allowing you to specify 2 to 127 criteria instead of just one. For example, the SUMIF function can set criteria for sellers who have sold more than 250 units. The SUMIFS feature can meet the criteria for sellers from a particular region who have sold more than 250 units.

Remark † These instructions apply to Excel 2019, 2016, 2013, 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, Excel for Office 365, and Excel Online.

Content
  1. How the SUM function works
  2. Data Entry Tutorial
  3. SUM Function Syntax
  4. SUM Function Arguments
  5. Running the SUMIFS function
  6. Entering the Sum_range argument
  7. self study
  8. Enter Criteria_range1 argument
  9. self study
  10. Criteria for argument input1
  11. self study
  12. How Cell References Increase the Versatility of SUMIFS
  13. Enter Criteria_range2 argument
  14. Criteria input2 criteria
  15. Add search criteria and complete the tutorial

How the SUM function works

SUMIFS usually works with rows of data, called records. A record links all the data in every cell or field in a row, such as company name, address, and phone number.

The SUMIFS argument looks for specific criteria in two or more fields in a record, and only if it finds a match for each specified field, does it summarize the data for that record.

In the SUMIF walkthrough, we met the single criterion of sales agents selling more than 250 orders per year.

In this tutorial, we set two conditions using SUMIFS: for sales agents in the East sales region who had fewer than 275 sales in the past year.

You can set more than two conditions by providing the additional Criteria_range and Criteria arguments to SUMIFS.

Data Entry Tutorial

The first step to use the SUMMER function in Excel is data entry.

Enter data in cells D1 to F11 Excel worksheet as shown in the image above.

The SUMMER function and search criteria (less than 275 orders and sales agents from the eastern sales region) are located in line 12 below the data.

Remark † The tutorial instructions do not include steps for formatting a worksheet. Although the formatting will not affect the completion of the tutorial, your worksheet will look different from the example. The SUM function will give you the same results.

SUM Function Syntax

In Excel, function syntax refers to the layout of the function and includes the function name, parentheses, and arguments.

The syntax for the SUM function is:

 = SUMIFS (Sum_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...) 

A maximum of 127 pairs can be specified in the function Criteria_range criteria

SUM Function Arguments

The function’s arguments specify which conditions to test and which range of data to summarize when those conditions are met.

All arguments in this function are required.

sum_range: the data in that range of cells is added together when a match is found between all specified criteria and their respective Criteria_range arguments.

Criteria_range: the group of cells in which the function should match the corresponding Criteria argument.

criteria: this value is compared with the data in the corresponding one.

Criteria_range: the actual data or cell reference for the argument data.

Running the SUMIFS function

While it’s possible to just use the SUMMER function directly in a cell in a worksheet, many people find it easier to use the function’s dialog box to enter the function.

  1. Click on cell F12 to make it the active cell; F12 is where you will access the SUMMER function.

  2. Open a tab formulas

  3. Click Mathematics and Triggers in the Function Library group.

  4. Click SUM in the list to open the SUM function dialog box.

Excel Online does not have a Formulas tab. To use SUMIFS in Excel Online, go to Insert Function

  1. Click on a cell F12 to make it active so that you can access the SUM function.

  2. Press the button Insert function † The Insert Function dialog box opens.

  3. Click Mathematics and Triggers in the category list.

  4. Click SUM in the list to perform the function.

The data we enter on the empty lines in the dialog are the arguments for the SUM function.

These arguments tell the function what conditions we are testing against and what range of data to summarize when it meets those conditions.

Entering the Sum_range argument

Argument sum_range contains links to the data cells we want to add.

This tutorial places the data for the Sum_range argument in the Total Sales column.

self study

  1. Click on a line sum_range in the dialog box.

  2. Highlight cells F3 to F9 on the worksheet to add those cell references to the row Sum_range.

Enter Criteria_range1 argument

In this tutorial, we’ll try to meet two criteria with every data entry:

  1. Sales agents from the East sales region

  2. Sellers selling less than 275 this year

The Criteria_range1 argument specifies the range of cells that SUMIFS should search for when searching for the first criterion: Sales Region East.

self study

  1. Click on a line Criteria_range1 in the dialog box.

  2. Highlight cells D3 to D9 on the worksheet to enter those cell references as the range the function will look for.

Criteria for argument input1

The first criterion we want to meet is whether the data is in the range D3:D9 East

While factual data such as the word East , can be entered into the dialog box for this argument, it is usually better to add the data to a cell in the worksheet and then enter that cell reference into the dialog box. box.

self study

  1. Click on a line Criteria1 in the dialog box.

  2. Click on a cell D12 to enter a reference to that cell. The function searches the range selected in the previous step for data that matches those criteria.

How Cell References Increase the Versatility of SUMIFS

When a cell reference such as D12 is entered as an argument criteria the SUM function matches all the data in that cell on the worksheet.

So, after determining the number of sales for the region East it’s easy to find the same data for a different sales region by simply changing from east to north or west in cell D12. The function is automatically updated and the new result is displayed.

Enter Criteria_range2 argument

The Criteria_range2 argument specifies the range of cells that SUMIFS should look for when it tries to meet the second criterion: sales agents who have sold fewer than 275 orders this year.

  1. Click on a line Criteria_range2 in the dialog box.

  2. Highlight cells from E3 to E9 on the worksheet to enter those cell references as the second range the function looks for.

Criteria input2 criteria

The second standard we want to meet is if the data in the E3:E9 range is less than 275 sales orders.

As with the Criteria1 argument, we will enter the cell reference for the Criteria2 location in the dialog box, not the data itself.

  1. Click on a line Criteria2 in the dialog box.

  2. Click on a cell E12 to enter a reference to that cell. The function searches the range selected in the previous step for data that matches the criteria.

  3. Click Okay to end the SUM function and close the dialog box.

The answer zero (0) appears in cell F12 (the cell where we entered the function) because we have not yet added data to the Criteria1 and Criteria2 fields (C12 and D12). Until we do this, the function won’t add, so the final value remains at zero.

Add search criteria and complete the tutorial

The last step in the tutorial is to add data to the cells in the sheet defined as containing arguments criteria

See the image above for help with this example.

  1. In a cell D12 enter East and press the key Input on keyboard.

  2. In a cell E12 enter and press the key Enter on keyboard (”

The answer $119,719.00 should appear in cell F12.

Only two entries, those in rows 3 and 4, meet both criteria, and therefore only the sales amounts for those two entries are added up by the function.

The sum of $49,017 and $70,702 is $119,719.

If you click cell F12, the entire function
=SUMIF(F3:F9, D3:D9, D12, E3:E9, E12) appears in the formula bar above the worksheet.

Leave a Reply

Your email address will not be published.