The SUMIFS function extends the usefulness of the SUMIF function by allowing you to specify from 2 to 127 criteria rather than just one. For example, the SUMIF function could set the criteria as salespeople who had sold over 250 units. The SUMIFS function could have the criteria as salespeople from a particular region who had sold over 250 units.

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.

## How the SUMIFS Function Works

Usually, SUMIFS works with rows of data called records. In a record, all of the data in each cell or field in the row is related, such as a company's name, address and phone number.

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

In the SUMIF step by step tutorial, we matched the single criterion of sales agents who had sold more than 250 orders in a year.

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

Setting more than two conditions can be done by specifying additional Criteria_range and Criteria arguments for SUMIFS.

## Entering the Tutorial Data

The first step to using the SUMIFS function in Excel is to input the data.

The SUMIFS function and the search criteria (less than 275 orders and sales agents from the East sales region) goes in row 12 below the data.

The tutorial instructions do not include formatting steps for the worksheet. While formatting will not interfere with completing the tutorial, your worksheet will look different than the example shown. The SUMIFS function will give you the same results.

## The SUMIFS Function's Syntax

In Excel, a function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the SUMIFS function is:

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

Up to 127 *Criteria_range* / *Criteria* pairs can be specified in the function.

## The SUMIFS Function's Arguments

The function's arguments tell it which conditions to test for and what range of data to sum when it meets those conditions.

All arguments in this function are required.

**Sum_range **—** **the data in this range of cells is summed when a match is found between all specified Criteria and their corresponding Criteria_range arguments.

**Criteria_range **—** **the group of cells the function is to search for a match to the corresponding Criteria argument.

**Criteria **—** **this value is compared with the data in the corresponding.

**Criteria_range **—** **actual data or the cell reference to the data for the argument.

## Starting the SUMIFS Function

Although it is possible to just the SUMIFS function directly into a cell in a worksheet, many people find it easier to use the function's dialog box to enter the function.

- Click cell F12 to make it the active cell; F12 is where you will enter the SUMIFS function.
- Click the
**Formulas**tab. - Click
**Math & Trig**in the Function Library group. - Click
**SUMIFS**in the list to bring up the SUMIFS function's dialog box.

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

- Click cell
**F12**to make it the active cell so you can enter the SUMIFS function. - Click the
**Insert Function**button. The Insert Function dialog box opens. - Click
**Math & Trig**in the Categories list. - Click
**SUMIFS**in the list to start the function.

The data that we enter into the blank lines in the dialog box will form the arguments of the SUMIFS function.

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

## Entering the Sum_range Argument

The **Sum_range** argument contains the cell references to the data we want to add up.

In this tutorial, the data for the Sum_range argument goes in the Total Sales column.

#### Tutorial Steps

- Click the
**Sum_range**line in the dialog box. - Highlight cells
**F3 to F9**in the worksheet to add these cell references to the Sum_range line.

## Entering the Criteria_range1 Argument

In this tutorial we are trying to match two criteria in each data record:

- Sales agents from the East sales region
- Sales agents who have made fewer than 275 sales this year

The Criteria_range1 argument indicates the range of cells the SUMIFS is to search when trying to match the first criteria: the East sales region.

#### Tutorial Steps

- Click the
**Criteria_range1**line in the dialog box. - Highlight cells D3 to D9 in the worksheet to enter these cell references as the range to be searched by the function.

## Entering the Criteria1 Argument

The first criteria we are looking to match is if data in the range D3:D9 equals *East*.

Although actual data, such as the word *East*, can be entered into the dialog box for this argument it is usually best to add the data to a cell in the worksheet and then input that cell reference into the dialog box.

#### Tutorial Steps

- Click the
**Criteria1**line in the dialog box. - Click cell
**D12**to enter that cell reference. The function will search the range selected in the previous step for data that matches these criteria.

#### How Cell References Increase SUMIFS Versatility

If a cell reference, such as D12, is entered as the *Criteria* Argument, the SUMIFS function will look for matches to whatever data is in that cell in the worksheet.

So after finding the sales amount for the *East* region, it will be easy to locate the same data for another sales region simply by changing East to North or West in cell D12. The function will automatically update and display the new result.

## Entering the Criteria_range2 Argument

The Criteria_range2 argument indicates the range of cells the SUMIFS is to search when trying to match the second criteria: sales agents who have sold fewer than 275 orders this year.

- Click the
**Criteria_range2**line in the dialog box. - Highlight cells
**E3 to E9**in the worksheet to enter these cell references as the second range to be searched by the function.

## Entering the Criteria2 Argument

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

As with the Criteria1 argument, we will enter the cell reference to Criteria2's location into the dialog box rather than the data itself.

- Click the
**Criteria2**line in the dialog box. - Click cell
**E12**to enter that cell reference. The function will search the range selected in the previous step for data that matches the criteria. - Click
**OK**to complete the SUMIFS function and close the dialog box.

An answer of zero (0) will appear in cell F12 (the cell where we entered the function) because we have not yet added the data to the Criteria1 and Criteria2 fields (C12 and D12). Until we do, there is nothing for the function to add up, and so the total stays at zero.

## Adding the Search Criteria and Completing the Tutorial

The last step in the tutorial is to add data to the cells in the worksheet identified as containing the *Criteria* arguments.

For help with this example see the image above.

- In cell
**D12**type**East**and press the**Enter**key on the keyboard. - In cell
**E12**type**<275**and press the**Enter**key on the keyboard (the " < " is the symbol for less than in Excel).

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

Only two records, those in rows 3 and 4 match both criteria and, therefore, only the sales totals for those two records are summed by the function.

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

When you click on cell F12, the complete function =SUMIFS(F3:F9,D3:D9,D12,E3:E9,E12) appears in the formula bar above the worksheet.