Excel SUMIF Function Adds Values That Meet Your Specified Criteria

Directly Above Shot Of Numbers On Table
Benjamin Dupont / EyeEm / Getty Images

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.

of 06

The SUMIF Function's Syntax

Screenshot of Excel showing the SUMIF Formula

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.

of 06

Entering the Tutorial Data

Screenshot of Excel showing 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.

of 06

Starting the SUMIF Function

Screenshot of Excel showing the Formula Select

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.

  1. Click on cell E12 to make it the active cell — this is where we will enter the SUMIF function.
  2. Click on the Formulas tab.
  3. Click on the Math & Trig icon on the ribbon to open the Formula Builder.
  4. Click on SUMIF in the list to begin crafting the function.

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.

of 06

Entering the Range and Criteria

Screenshot of Excel showing 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.

  1. In the Formula Builder, click on the Range line.
  2. Highlight cells D3 to D9 on the worksheet to enter these cell references as the range to be searched by the function.
  3. Click on the Criteria line.
  4. 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.

of 06

Entering the Sum_range Argument

Screenshot of Excel showing the Sum_range

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.

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

Once data is entered into cell D12 in the next step, if the Range field of a record contains a match for the criteria in D12 then the data in the Total Sales field for that record will be added to the total by the function.

of 06

Adding the Search Criteria

Screenshot of Excel showing 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) 
  1. In cell D12 type > 250 and press the Enter key on the keyboard.
  2. The answer $290,643.00 should appear in cell E12.
  3. 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.