• Share
Send to a Friend via Email

### Your suggestion is on its way!

An email with a link to:

was emailed to:

Thanks for sharing About.com with others!

# Excel SUMIFS Function

## using the SUMIFS function to Sum Data

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.

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

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

Ted French