The SUMIFS function works like the AND function in that it only sums the data if the criterion for each range is true.
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 counted or not.
Note: Up to 127 range/criteria pairs can be entered into the function.
Example Using Excel's SUMIFS Function:
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.