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

• You can use SUMIF in Google Sheets to add numbers together that are associated with a date range, specific text, or meet a numeric criterion.
• Here's everything you need to know to use the SUMIF function in Google Sheets.

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.

How to use SUMIF with a simple number condition

Even with the optional sum_range argument, the SUMIF function isn't difficult to use, but the easiest way to see it in action is by adding a range of values based on a criterion in that range. For example, suppose you have a spreadsheet like this one, and you are interested in the sum of all the numbers that are 100 or higher.

You can use SUMIF to sum together values based on certain criteria. Dave Johnson/Insider

1. Type "=SUMIF" and press the Tab key. Google Sheet will automatically add the open parenthesis and wait for the range.
2. Click and drag the mouse to select the column with the numbers you want to sum.
3. Type a comma and then enter ">=100" (including the quotes).
4. Press the Tab key. Google Sheets will add the closing parenthesis and you should see the result in the cell. We didn't need to specify a sum_range because in this example it's just the same as the range.
   
   

A simple version of the SUMIF function totaling numbers in which the sum_range is just the range. Dave Johnson/Insider

How to use SUMIF with a text condition

Suppose you had a similar spreadsheet, but it was a tally of sales by region. If you only wanted the sum of sales in the east region, you could use SUMIF with a text criterion, and add the optional sum_range to specify where the argument should find the values to sum. Consider this spreadsheet:

In this spreadsheet, you can easily use SUMIF to total only numbers that correspond to a cell in a nearby column. Dave Johnson/Insider

1. Type "=SUMIF" and press the Tab key. Google Sheet will automatically add the open parenthesis.
2. Click and drag the mouse to select the column with the names of the regions.
3. Type a comma and then enter "East" (including the quotes).
4. Type a comma and then select the column with the sales figures.
5. Press the Tab key. Google Sheets will add the closing parenthesis and you should see the result in the cell.
   

   

   We're using the optional sum_range to indicate which column has the vales we want to add. Dave Johnson/Insider

How to use SUMIF with a date condition

The SUMIF function can even sum numbers based on a date - such as values related to a specific date, or before or after a date. Suppose we want to total all the sales that happened on January 15.

You can make dates a condition in the SUMIF function. Dave Johnson/Insider

1. Type "=SUMIF" and press the Tab key. Google Sheet will automatically add the open parenthesis.
2. Click and drag the mouse to select the column with the range of dates.
3. Type a comma and then enter "DATE(2021, 1,15)" (do not include the quotation marks).
4. Type a comma and then select the column with the sales figures.
5. Press the Tab key. Google Sheets will add the closing parenthesis and you should see the result in the cell.
   
   

Be careful about your syntax when you enter a date or you might get an unexpected result. Dave Johnson/Insider

In this example, we added together all the numbers that occurred on January 15, 2021, but you can also sum the numbers that happened before that date or after that date.

To sum everything on or before that date, enter the criterion like this: "<="&DATE(2021, 1,15). In this case, the less than and equals to symbols are "concatenated" to the date with the ampersand (&) symbol, and the less than and equals to symbols are in quotes.

Likewise, to sum everything after that date, enter the criterion like this: ">"&DATE(2021, 1,15).

You can concatenate logical operators with the date in SUMIF. Dave Johnson/Insider

How to use SUMIF with a wildcard

Wildcards are handy because they let you work with partial matches, such as any product that has the word "apple" in it.

In this spreadsheet, we'll sum only the sales for apple-related products. Dave Johnson/Insider

1. Type "=SUMIF" and press the Tab key. Google Sheet will automatically add the open parenthesis.
2. Click and drag the mouse to select the column with the names of the products.
3. Type a comma and then enter "*apple*" (include the quotation marks).
4. Type a comma and then select the column with the sales figures.
5. Press the Tab key. Google Sheets will add the closing parenthesis and you should see the result in the cell.
   
   

Note how the wildcard is used in the criteria. Dave Johnson/Insider

In this example, we are looking for the word "apple" to appear anywhere in the cell, so we put an asterisk both before and after the word - this way it included apple at the end of the cell, like "Green Apples," and apple at the start of the cell, like "Apple Butter." If you prefer, you could write just "*apple" to only include cells in which apple appears at the end of the cell.

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.

Read the original article on Business Insider

Via PakApNews