It’s easy to add a set of numbers together – every spreadsheet user knows how to use the tried-and-true SUM function to find a total. But what if the sum you’re trying to find depends on some sort of condition? Suppose, for example, you have a set of numbers and you only want to add up the ones that are below a certain max value. Or perhaps you have your company’s sales tally and want to know only the sales from a particular region or sales from a certain time period.
That’s where Google Sheets’ SUMIF function comes in. You can use SUMIF to calculate a sum based on a condition. That condition can be built into the set of values themselves, or numbers that are related to a neighboring row or column. If that sounds complicated, the good news is that it’s easy to apply.
How to use the SUMIF function in Google Sheets
As the name of the function implies, SUMIF is conditional and checks for a status using the IF function before totaling your numbers. This is what the function looks like:
=SUMIF(range, criterion, [sum_range])
Range: The range is the set of cells that you want to test against some sort of criterion.
Criterion: This is what you want to use to test against the range. The SUMIF function is pretty versatile – you can use a number, text, or even a date as the criterion.
Sum_range: The sum_range is optional, and is what gives this function so much power. If you omit the sum_range, the function will sum the range. But you have the option of summing a different range depending on the result of the conditional test.
Tips for using SUMIF in Google Sheets
Once you’ve used the SUMIF function a few times, you’ll probably find that it’s pretty straightforward, both with and without the optional argument. But here are a few tips to keep in mind to get the most out of SUMIF:
The SUMIF function can only be used to evaluate one condition. If you need to work with several criteria at once, you might need to switch to the SUMIFS function.
When you use the optional sum_range, it doesn’t have to be right next to the range, but it does need to include the same number of cells.
If you include a text argument in SUMIF, it isn’t case-sensitive – and you can’t make it case-sensitive, so it will treat “apple,” “Apple,” and “APPLE” the same way.
Remember to use quotes to enclose elements like text and logical operators, like “apple” and “>=1”
If you need to combine two elements in the argument – like a greater than operator and date, for example, use an ampersand to join them.
