Excel SUMIF: Sum Values that Meet Specified Criteria

01
of 08

How the SUMIF Function Works

Excel SUMIF Function Tutorial
Excel SUMIF Function Tutorial. © Ted French

SUMIF Function Overview

The SUMIF function combines the IF function and SUM function in Excel. This combination allows you to add up those 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.

SUMIF Function Step by Step Tutorial

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.

Tutorial Topics

  • Entering the Tutorial Data
  • The SUMIF Function's Syntax
  • Starting the SUMIF Function
  • Entering the Range Argument
  • Entering the Criteria Argument
  • Entering the Sum_range Argument
  • And the Answer is ..
02
of 08

Entering the Tutorial Data

Excel SUMIF Function Tutorial
Excel SUMIF Function Tutorial. © Ted French

Entering the Tutorial Data

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.

Note: 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.

03
of 08

The SUMIF Function's Syntax

The SUMIF Function's Syntax
The SUMIF Function's Syntax. © Ted French

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 SUMIF Function's Arguments

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 - this value is 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.

04
of 08

Starting the SUMIF Function

>Opening the SUMIF Function dialog box
>Opening the SUMIF Function dialog box. © Ted French

Opening the SUMIF Function dialog box

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

Tutorial Steps

  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 function drop down list.
  4. Click on SUMIF in the list to bring up the SUMIF function's dialog box.

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.

05
of 08

Entering the Range Argument

Entering the Range Argument
Entering the Range Argument. © Ted French

Entering the Range Argument

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.

Tutorial Steps

  1. In the dialog box, 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.
06
of 08

Entering the Criteria Argument

Excel SUMIF Function Tutorial
Excel SUMIF Function Tutorial. © Ted French

Entering the Criteria Argument

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.

Tutorial Steps

  1. Click on the Criteria line in the dialog box.
  2. 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 criteria (North).

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 " . The function will automatically update and display the new result.

07
of 08

Entering the Sum_range Argument

Entering the Sum_range Argument
Entering the Sum_range Argument. © Ted French

Entering the Sum_range Argument

The Sum_range argument is the group of cells that the function is to sum up when it finds a match in the Range argument identified in step 5 of the tutorial.

 

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.

Tutorial Steps

  1. Click on the Sum_range line in the dialog box.
  2. Highlight cells E3 to E12 on the spreadsheet to enter these cell references as the Sum_range argument.
  3. Click OK to close the dialog box and complete the SUMIF function.
  4. An answer of zero should appear in cell E12 - the cell where we entered the function - because we have not yet added the data to the Criteria field (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 - the data in the Total Sales field for that record will be added to the total by the function.

08
of 08

Adding the Search Criteria

Adding the Search Criteria
Adding the Search Criteria. © Ted French

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.

Tutorial Steps

  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. 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.
  3. When you click on cell E12, the complete function
    =SUMIF (D3:D9, D12, E3:E9) appears in the formula bar above the worksheet.
  4. To find the sales total for different numbers of order, type the amount, such as in cell E12 and press the Enter key on the keyboard.
  5. The total sales for the appropriate number of cells should appear in cell E12.
Format
mla apa chicago
Your Citation
French, Ted. "Excel SUMIF: Sum Values that Meet Specified Criteria." ThoughtCo, May. 16, 2017, thoughtco.com/excel-sumif-sum-values-3124005. French, Ted. (2017, May 16). Excel SUMIF: Sum Values that Meet Specified Criteria. Retrieved from https://www.thoughtco.com/excel-sumif-sum-values-3124005 French, Ted. "Excel SUMIF: Sum Values that Meet Specified Criteria." ThoughtCo. https://www.thoughtco.com/excel-sumif-sum-values-3124005 (accessed January 18, 2018).