The SUMIFS function works like the AND function in that it only sums the data if the criterion for each range is true.
The SUMIFS Function's Syntax and Arguments
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:
- 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
- Highlight 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
- Highlight cells F1 to F4 on the worksheet
- 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
- Highlight cells G1 to G4 on the worksheet
- 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 to complete the function and close the dialog box
- The answer 240 should appear in cell H1
- When you click on cell H1, the complete function
= SUMIFS ( E1 : E4 , F1 : F4 , " > 15 " , G1 : G4 , " > 5 " ) appears in the formula bar above the worksheet