Adding values in Google Sheets is easy enough to do using the SUM function. But what if you only want to add numbers that meet certain criteria? By using SUMIF in a Google Sheets formula, you can add the exact values you want.
SUMIF is one of those features that can save you time in manual work. Instead of searching through your data and manually adding the numbers you need, you can enter a formula using the SUMIF function. The criteria you use in the formula can be a number or text. In addition, you can add values in another range of cells based on the criteria in another. This is how it works.
Use the SUMIF function in Google Sheets
The syntax for the function is
SUMIF(cell_range, criteria, sum_range) where the first two arguments are required. You can use the
sum_range argument for adding cells in a range other than the lookup range. Let’s look at some examples.
RELATED: Find data in Google Sheets with VLOOKUP
To start simply, we want to sum the values in the range B2 through B12 if they are greater than 450. You would use this formula:
The first part of the formula in parentheses is the range of cells we are looking for the values. The “>450” section looks for values greater than 450 in that range. The SUMIF function then adds the values that meet those conditions.
You can use the greater than, less than, or equal to operator in your formula. With a little tweak, this formula adds the values if they are greater than or equal to 450:
Perhaps the values you want to add are based on criteria in a different range of cells. This is when you would use the
Use this formula to add the values in the cell range B2 to B12 only if the value in the range A2 to A12 is Location A:
For another example that uses the optional argument, we want to add the values in the range F2 through F12 only if a value in B2 through B12 is greater than or equal to 500. Here’s the formula:
The next time you need to add values to your sheet but want to make sure you only include certain values, try the SUMIF function in Google Sheets. Also check out how to see basic calculations without formulas in Google Sheets!