Related Tutorial: Step by Step Tutorial with Screen Shots - Excel SUMIFS Function
The SUMIFS function adds up the data in selected cells when specific criteria are met in two or more ranges of data.
How the SUMIFS Function Works
The SUMIFS function extends the SUMIF function by allowing you to set conditions on two or more ranges of data rather than just the one in SUMIF.
Normally SUMIFS works with rows of data called records. In a record, all of the data in each cells or field in the row is related - such as a company's name, address and phone number.
SUMIFS looks for specific criteria in two or more fields in the record and only if it finds a match for each field specified is the data for that record added up.
SUMIFS Function Syntax
The syntax for the SUMIFS function is:
=SUMIFS ( Sum_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)
Sum_range - the range of cells to be added.
Criteria_range - the range of cells the function is to search.
Criteria - determines whether a cell in the Sum_range is to be summed or not.
Note: Up to 127 range/criteria pairs can be entered into the function.
Example: Using Excel's SUMIFS Function
In this example we will search cells F1 to F4 for values greater than 15 and cells G1 to G4 for values that are greater than 5.
For those records that meet both conditions, we will sum the corresponding numbers in cells E1 to E6.
For help with this example, see the image above.
- Enter the following data into cells E1 to E4: 80, 100, 120, 140.
- Enter the following data into cells F1 to F4: 22, 33, 12, 17.
- Enter the following data into cells G1 to G4: 1, 7, 5, 6.
- Click on cell H1 - the location where the results will be displayed.
- Click on the Formulas tab.
- Choose Math & Trig from the ribbon to open the function drop down list.
- Click on SUMIFS in the list to bring up the function's dialog box.
- Click on the Sum_range line in the dialog box.
- Drag select cells E1 to E4 on the spreadsheet to add that range to the Sum_range line.
- Click on the Criteria_range1 line in the dialog box.
- Drag select cells F1 to F4 on the spreadsheet.
- Click on the Criteria1 line in the dialog box.
- Type " > 15 " on the Criteria1 line. This tells the function to only look at data in cells F1 to F4 that is greater than 15.
- Click on the Criteria_range2 line in the dialog box.
- Drag select cells G1 to G4 on the spreadsheet.
- Click on the Criteria2 line in the dialog box.
- Type " > 5 " on the Criteria2 line. This tells the function to only look at data in cells G1 to G4 that is greater than 5.
- Click OK.
- The answer 240 should appear in cell H1. This is because only two of the four rows of data (rows 2 and 4) match both criteria: a number in column F that is greater than 15 and a number in column G that is greater than 5.
- The SUMIFS function, therefore, adds only the data in cells E2 and E4 which equals 240.
- The complete function = SUMIFS ( E1 : E4 , F1 : F4 , " > 15 " , G1 : G4 , " > 5 " ) appears in the formula bar above the worksheet when you click on cell H1.
More Something - IF Function Quick Tutorials
