1. Computing

Excel SUMIFS Function

using the SUMIFS function to Sum Data

By

Excel SUMIFS Function

Excel SUMIFS Function

© Ted French

The SUMIFS function adds up the data in selected cells when specific criteria are met in two or more ranges of data.

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.

  1. Enter the following data into cells E1 to E4: 80, 100, 120, 140.

  2. Enter the following data into cells F1 to F4: 22, 33, 12, 17.

  3. Enter the following data into cells G1 to G4: 1, 7, 5, 6.

  4. Click on cell H1 - the location where the results will be displayed.

  5. Click on the Formulas tab.

  6. Choose Math & Trig from the ribbon to open the function drop down list.

  7. Click on SUMIFS in the list to bring up the function's dialog box.

  8. Click on the Sum_range line in the dialog box.

  9. Drag select cells E1 to E4 on the spreadsheet to add that range to the Sum_range line.

  10. Click on the Criteria_range1 line in the dialog box.

  11. Drag select cells F1 to F4 on the spreadsheet.

  12. Click on the Criteria1 line in the dialog box.

  13. 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.

  14. Click on the Criteria_range2 line in the dialog box.

  15. Drag select cells G1 to G4 on the spreadsheet.

  16. Click on the Criteria2 line in the dialog box.

  17. 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.

  18. Click OK.

  19. 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.

  20. The SUMIFS function, therefore, adds only the data in cells E2 and E4 which equals 240.

  21. 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.


Related Tutorials

©2014 About.com. All rights reserved.