The **SUMIF** function combines the **IF** and **SUM** functions in Excel; this combination allows you to add up values in a selected range of data that meets specific criteria. The **IF **portion of the function determines what data meets the specified criteria and the **SUM** part does the addition.

Normally, **SUMIF** is used with rows of data called **records**. In a record, all of the data in each cell in the row is related — such as a company's name, address and phone number. **SUMIF** looks for the specific criteria in one cell or field in the record and, if it finds a match, it adds that data or data in another specified field in the same record.

### The SUMIF 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 **SUMIF** function is:

= SUMIF ( Range, Criteria, Sum_range )

The function's arguments tell the function what condition we are testing for and what range of data to sum when the condition is met.

**Range:** The group of cells the function is to search.

**Criteria: **The value to be compared with the data in the **Range** cells. If a match is found, then the corresponding data in the **sum_range** is added up. Actual data or the cell reference to the data can be entered for this argument.

**Sum_range** (optional): The data in this range of cells is added up when matches are found between the **range** argument and the **criteria**. If this range is omitted, the first range is summed instead.

### Entering the Tutorial Data

This tutorial uses a set of data records and the **SUMIF** function to find the total yearly sales for Sales Reps who have sold more than 250 orders. Following the steps in the tutorial topics below walks you through creating and using the **SUMIF** function seen in the image above to calculate total yearly sales.

The first step to using the **SUMIF** function in Excel is to enter the data. Enter the data into **cells** **B1 **to **E11 **of an Excel worksheet as seen in the image above. The **SUMIF** function and the search criteria (greater than 250 orders) will be added to row 12 below the data.

The tutorial instructions do not include formatting steps for the worksheet. This will not interfere with completing the tutorial. Your worksheet will look different than the example shown, but the **SUMIF** function will give you the same results.

### Starting the SUMIF Function

Although it is possible to just type the **SUMIF** function into a cell in a worksheet, many people find it easier to use the **Formula Builder** to enter the function.

- Click on
**cell****E12**to make it the active cell — this is where we will enter the**SUMIF**function. - Click on the
**Formulas tab**. - Click on the
**Math & Trig**icon on the ribbon to open the**Formula Builder**. - Click on
**SUMIF**

The data that we enter into the three blank rows in the dialog box will form the arguments of the **SUMIF** function; these arguments tell the function what condition we are testing for and what range of data to sum when the condition is met.

### Entering the Range and Criteria

In this tutorial we want to find the total sales for those Sales Reps who had more than 250 orders in the year. The Range argument tells the **SUMIF** function which group of cells to search when trying to find the specified criteria of **> 250**.

- In the
**Formula Builder**, click on the**Range**line. - Highlight
**cells D3**to**D9**on the worksheet to enter these cell references as the range to be searched by the function. - Click on the
**Criteria**line. - Click on
**cell****E13**to enter that cell reference. The function will search the range selected in the previous step for data that matches this criterion (North).

In this example, if data in the range **D3:D12** is greater than 250 then the total sales for that record will be added by the **SUMIF** function.

Although actual data - such as text or numbers like* ***> 250** 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 enter that cell reference into the dialog box.

### Cell References Increase Function Versatility

If a cell reference, such as **E12**, is entered as the Criteria Argument, the **SUMIF** function will look for matches to whatever data has been typed into that cell in the worksheet.

So after finding the total sales for Sales Reps with more than 250 orders, it will be easy to find the total sales for other order numbers, such as less than 100, simply by changing **> 250** to **< 100**.

### Entering the Sum_range Argument

The **Sum_range** argument is the group of cells that the function will sum up when it finds a match in the **Range** argument; this argument is optional and, if omitted, Excel adds the cells that are specified in the **Range** argument.

Since we want the total sales for Sales Reps with more than 250 orders we use the data in the **Total Sales** column as the **Sum_range** argument.

- Click on the
**Sum_range**line. - Highlight
**cells E3**to**E12**on the spreadsheet to enter these cell references as the**Sum_range**argument. - Click Done to complete the SUMIF function.
- An answer of
**0****cell****E12**— the cell where we entered the function because we have not yet added the data to the**Criteria**field of**D12**,

### Adding the Search Criteria

The last step in the tutorial is to add the criteria we want the function to match. In this case, we want the total sales for Sales Reps with more than 250 orders so we will add the term **> 250** to **D12** — the cell identified in the function as containing the criteria argument.

=SUMIF (D3:D9, D12, E3:E9)

- In
**cell D12**type**> 250**and press the**Enter**key on the keyboard. - The answer
**$290,643.00**should appear in**cell E12**. - When you click on
**cell E12**, the complete function, as shown above, appears in the formula bar above the worksheet.

The criterion of **> 250** is met in four cells in **column D: D4, D5, D8, D9**. As a result the numbers in the corresponding cells in column **E: E4, E5, E8, E9 **are totaled.

To find the sales total for different numbers of order, type the amount in **cell E12*** *and press the **Enter** key on the keyboard. The total sales for the appropriate number of cells should then appear in **cell E12**.