Excel's SUMIF Function Adds Values That Meet Your Specified Criteria

Wooden blocks that show I + I = II

Benjamin Dupont/EyeEm/Getty Images

The SUMIF function combines the IF and SUM functions in Excel to allow you to add up values in a selected range of data that meet specific criteria. The IF portion of the function determines what data matches the specified criteria and the SUM part does the addition.

For example, you might want to total up annual sales, but only by reps who had more than 250 orders.

It's common to use SUMIF 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.

These instructions apply to Excel 2019, 2016, 2013, 2010, and Excel for Office 365.

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.

Screenshot of Excel showing the SUMIF Formula

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 it meets them.

  • Range (required) is the group of cells you want to evaluate against the criteria.
  • Criteria (required)The value that the function will compare with the data in the Range cells. If it finds a match, then it will add up the corresponding data in the sum_range. You can enter actual data or the cell reference to the data for this argument.
  • Sum_range (optional): The function adds up the data in this range of cells when it finds matches; if you omit this range, it will sum the first range.

For example, if your Criteria is sales reps that have sold more than 250 units, you would set the Sum_range as a column that lists the sales numbers by rep. If you left Sum_range blank, the function would total up the #Orders column.

Entering the Data for the SUMIF Function

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.

Screenshot of Excel showing the tutorial data

The first step to using the SUMIF function in Excel is to enter the data. In this case, the data for goes into cells B1 to E11 of your worksheet as seen in the image above. That will be the Range argument. The Criteria (>250) will go into cell D12, as seen below.

Screenshot of Excel showing the Search Criteria

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

Although you can type the SUMIF function into a cell in a worksheet, many users find it easier to use the Function Dialog Box to enter the function.

  1. Click on cell E12 to make it the active cell — this is where the SUMIF function goes.

  2. Click on the Formulas tab.

  3. Click on the Math & Trig icon on the ribbon to open the drop-down menu.

  4. Click on SUMIF in the list to open the Function Dialog Box.

    Excel's Math & Trig drop-down menu with SUMIF selected.
  5. The data that goes in the three blank rows in the dialog box will form the arguments of the SUMIF function; these arguments tell the function what condition you're testing for and what range of data to sum when the condition is met.

  6. In the Function Dialog Box, click on the Range line.

  7. Highlight cells D3 to D9 on the worksheet to enter these cell references as the range to be searched by the function.

  8. Click on the Criteria line.

  9. Click on cell D12 to enter that cell reference. The function will search the range selected in the previous step for data that matches this criterion (>250).

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

  11. Click on the Sum_range line.

  12. Highlight cells E3 to E12 on the spreadsheet to enter these cell references as the Sum_range argument.

  13. Click Done to complete the SUMIF function.

  14. Four cells in column D (D4, D5, D8, D9) meet the criterion of > 250. As a result, the numbers in the corresponding cells in column E: E4, E5, E8, E9 are totaled. The answer $290,643 should appear in cell E12.

  15. When you click on cell E12, the complete function, as shown above, appears in the formula bar above the worksheet.

Using a Cell Reference as Criteria

Although you can input actual data, such as text or numbers like > 250, into the Criteria line of 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.

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