1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

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.

Related Tutorial: Expanded Step by Step Excel SUMIFS Function Tutorial with Screen Captures

The SUMIFS Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, 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:

  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. Highlight 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. Highlight cells F1 to F4 on the worksheet

  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. Highlight cells G1 to G4 on the worksheet

  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 to complete the function and close the dialog box

  19. The answer 240 should appear in cell H1

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

The Logic behind the Answer

  • 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

©2014 About.com. All rights reserved.