Excel 2007 SUMIF Function

Related Tutorial: Step by Step Tutorial with Screen Shots - Excel 2007 SUMIF Function

The SUMIF function combines two of Excel's most popular functions - the SUM function and the IF function. What SUMIF will do is sum up data in a selected range of cells that meet specific criteria.

The syntax for the SUMIF function is:

= SUMIF ( Range, Criteria, Sum Range )

Range - the group of cells the function is to search.

Criteria - determines whether the data in a cell is to be summed up or not.

Sum Range - the data in this range will be summed if data in corresponding cells in the first range meets the specified criteria. If this range is omitted, the first range is summed instead.

Example Using Excel 2007's SUMIF Function:

In this example we will find the values in the range E1 to E6 that equal 165.

For those that do equal 165, we will sum up the corresponding numbers in cells F1 to F6.

Note: For help with this example see the image above.

1. Enter the following data into cells E1 to E6: 114, 165, 178, 143, 130, 165.
   
   
2. Enter the following data into cells F1 to F6: 10, 20, 30, 10, 20, 30.
   
   
3. Click on cell F7 - the location where the results will be displayed.
   
   
4. Click on the Formulas tab of the ribbon.
   
   
5. Choose Math & Trig from the ribbon to open the function drop down list.
   
   
6. Click on SUMIF in the list to bring up the function's dialog box.
   
   
7. In the dialog box, click on the Range line.
   
   
8. Drag select cells E1 to E6 on the spreadsheet to enter these cell references as the range to be searched by the function.
   
   
9. On the Criteria line in the dialog box, type "165". The function will search the range selected in step 8 for data that matches this criteria.
   
   
10. Click on the SUM Range line.
    
    
11. Drag select cells F1 to F6 on the spreadsheet. If the criteria specified in step 9 matches any data in the first range (E1 : E6), the function will add up the data in the corresponding cells in this second range of cells.
    
    
12. Click OK.
    
    
13. The answer 50 should appear in cell F7. Since the criterion of equaling 165 is met in only two cells - E2 and E6, only the numbers in cells F2 and F6 are summed. The sum of 20 and 30 is 50.
    
    
14. When you click on cell F7, the complete function
    = SUMIF ( E1 : E6 , "=165" , F1 : F6 ) appears in the formula bar above the worksheet.

• Excel 2007 COUNTIF
• Excel 2007 AVERAGEIF
• Excel IF Function Step by Step Tutorial