In Excel, the SUMIF and SUMIFS functions both sum the cells in a range that meet a certain condition, but they use slightly different behaviors:
- The SUMIF function sums the number of cells in a range that meet one condition.
- The SUMIFS function counts the number of cells in a range that meet several conditions.
The following examples show how to use each function in practice with the following data in Excel:
Example 1: Using SUMIF
We can use the following SUMIF formula to sum the values in the Points column where the value in the Team column is equal to “Mavs”:
=SUMIF(A2:A16, "Mavs", C2:C16)
The following screenshot shows how to use this formula in practice:
The sum of the points for the rows where team is equal to “Mavs” is 86.
Example 2: Using SUMIFS
We can use the following SUMIFS formula to sum the values in the Points column where the value in the Team column is equal to “Mavs” and the value in the Rebounds column is greater than 5:
=SUMIFS(C2:C16, A2:A16, "Mavs", B2:B16, ">5")
The following screenshot shows how to use this formula in practice:
The sum of points for the rows where team is equal to “Mavs” and rebounds is greater than 5 is 51.
Note: Refer to the Excel documentation page for a complete explanation of the SUMIF and SUMIFS functions.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Use SUMIF with OR in Excel
How to Use SUMIFS with a Date Range in Excel
How to Use Wildcard in SUMIFS Function in Excel
