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? Using SUMIF in a Google Sheets formula, you can add the exact values you want.
SUMIF is one of those functions that can save you time from manual work. Rather than scouring your data and manually adding the numbers you need, you can pop in a formula with the SUMIF function. The criteria you use in the formula can be a number or text. Plus you can add values in a different cell range based on the criteria in another. Here’s 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.
To start simple, we want to add 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 cell range we’re searching for the values. The “>450” portion looks for values greater than 450 in that range. The SUMIF function then adds the values meeting those conditions.
You can use the greater than, less than, or equal to operator in your formula. With a slight adjustment, this formula adds the values if they’re greater than or equal to 450:
Maybe the values you want to add are based on criteria in a different cell range. This is when you would use the
To add the values in the cell range B2 through B12 only if the value in the range A2 through A12 is Location A, you would use this formula:
For another example using 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 in your sheet but want to make sure you only include certain ones, try the SUMIF function in Google Sheets. Be sure to check out how to see basic calculations without formulas in Google Sheets too!