How to use the SUMIF function in Google Sheets to find a specific sum in your spreadsheet

typing on computer with spreadsheets
You can use SUMIF on Google Sheets to calculate a sum based on various conditions.

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.

How to use VLOOKUP in Google Sheets to search for specific data and replicate it across spreadsheetsHow to convert your Google Sheets files into Excel files, and edit them in Microsoft ExcelHow to see the edit history for cells in Google Sheets and spot changes or mistakes in a spreadsheetHow to add cells in Google Sheets on your PC or Mac

Read the original article on Business Insider