Table of Contents
SUMIF is a function in Excel used to sum the values in a range based on a single criteria. SUMIFS is a function in Excel used to sum the values in a range based on multiple criteria. SUMIFS can also be used for more complex criteria, such as checking whether a value is greater than, less than, or equal to a certain number.
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 and functions.