Most people often use the SUM and the IF formula together to sum up numbers if they fulfill a given condition. However, many don't know there’s a much simpler way to do this. You just have to use the SUMIF formula.

Read on to learn what the SUMIF formula is, its syntax, and how you can use it in Google Sheets.

What Is the Purpose of SUMIF Function?

SUMIF essentially combines two of the more straightforward functions in Google Sheets. The SUM and the IF function. It works similarly to the SUMIF function in Excel.

As the names of the two functions suggest, this function looks through a specified cell range and finds the ones that match a condition that the user specifies. If that condition is met, the values in those cells are extracted and added.

Before continuing, we’d recommend looking at how to do SUM functions and learning about the IF function. An understanding of these will help you master the SUMIF function more efficiently.

The SUMIF function's advantage is that you don't have to write in the two formulas manually as you can get the same output with just one formula, saving you time, hassle, and potential errors. If you’d like to learn more about summing in Google Sheets, check out the link for a complete guide.

Difference Between SUMIFS and SUMIF

Now that we know what the SUMIF function does, it's crucial to understand the differences between the SUMIF and the SUMIFS formula.

  • SUMIF: This formula is used when there is only a single condition to be met to sum up the values in the defined range.
  • SUMIFS: This formula is used if more than one condition needs to be met for the formula to be executed.

The SUMIF Function Syntax in Google Sheets.

There are three arguments used in SUMIF for Google Sheets:

=SUMIF(range, condition, sum_range)

Here is what each argument means in this formula:

  • range: This is the cell or the cell range to test to see if they meet a specific condition.
  • condition: this is a criterion that the cell in the first argument needs to fulfill to qualify as a match.
  • sum_range: This is an optional argument used to define the cell range that contains the values that sheets need to add if the condition defined is fulfilled. The first parameter is used here if this range is not defined.

To use SUMIF appropriately, you may need to learn operators to put into the condition field. But don’t worry, you can just follow this Google Sheets cheat sheet. There are essentially two ways to use this formula in your spreadsheet. The first one involves using the sum-range argument, while the second method doesn't have the sum-range defined.

If the sum_range is defined, the SUMIF formula takes the values in the sum_range and adds them to the final sum if the condition is met. If the first two arguments are given, the SUMIF function will go through every cell in the range and add only the cells that fulfill the condition.

SUMIF Examples

Although looking at the formula's syntax gives us a fair idea of how to use the formula, looking at the formula in action can lift your understanding. Let’s look at a few examples of the SUMIF formula in Google Sheets.

Example 1: A Numeric Condition

In this example, we have a sample range that contains negative and positive numbers. Here, we wish to use the SUMIF function to select only the positive numbers and add them. Here are the steps you need to follow to input the formula in this situation:

An example of using SUMIF with a numeric condition
  1. Click on the cell where you wish to input the formula, C2 in the example.
  2. Input the initial part of the formula, which is =SUMIF(
  3. Enter the first argument, which is the range. In this case, it is A2:A13.
  4. Now input the second argument, which is the condition. In this case, it is ">=0."
  5. Add the closing bracket.
  6. Press Enter to execute the formula.

In this formula, we define the second argument to check whether the value in a cell is greater or equal to zero using the >= operator. The cell range is defined in the first argument. We leave the third argument empty, as it is not required in this case.

If you wanted to instead sum up all the negative numbers in the data set, use “<=0” as the second argument.

Example 2: A Text Condition

We checked to see that a predefined number exists in the data range in the previous example. Now, we will look for specific text in a data set. Here are the steps you need to follow to build the formula in this situation:

An example of using SUMIF with a text condition
  1. Click on the cell where you wish to input the formula, D2 in the above example.
  2. Input the initial part of the formula, which is =SUMIF(
  3. Enter the first cell range to check. In this case, we write the cell range A2:A8.
  4. Now add a comma and write the condition to be met. In this case, we want to search for "Tea." Make sure you write this inside the quotation marks.
  5. In this example, we will need to use the sum_range so we'll write the cell range B2:B8.
  6. Add the closing bracket.
  7. Press Enter to execute.

In this example, we:

  • Checked the first argument and wrote the text range.
  • Used the second argument to define that we need to search for Tea.
  • Used the third argument to indicate the range from which sheets will take the values from. This is because the data we need is in a different cell range than the one we are checking for in argument two.

Example 3: Using the Not Equal To Operator

This is similar to the last example. Here we will search for values that are not equal to a defined value. Here we will use the not equal to operator of <> and a text string.

Here is what you need to do to build the formula in this situation:

An example of using SUMIF with the not equal to operator
  1. Click on the empty cell you want to add the formula to, D2 in the example.
  2. In the first argument, we write the cell range A2:A9.
  3. After a comma, write the second argument. In this case, we write the second argument as "<>John." (not equal to John)
  4. Define the third argument as the cell range containing the sales data, B2:B9.

Wrapping Up the SUMIF Function

SUMIF is an extremely useful function that combines two of the most used in Google Sheets. It's a highly versatile function with a lot of applications when building more complex formulas. Although it may be intimidating at first, it won’t take long to wrap your head around. Once you do, you’ll be ready to tackle some more demanding functions.