How To Use the SUMIF Google Sheets Function (3 Easy Examples)

Want to learn how to use the SUMIF function in Google Sheets? I created this guide to show exactly how to do it. While it’s easy to sum a range of numbers, even for a beginner to Google Sheets, it’s much more helpful to sum based on a condition.

Thankfully, the SUMIF Google Sheets function specifically helps with conditional sums. Once you understand the basic syntax of the sum formula and look at a few examples, you’ll want to use the Google spreadsheet SUMIF function every chance you get.

In this tutorial, I’ll explain the syntax for the Google Sheets SUMIF function and show a video on how to apply it in a real-world scenario. Plus I’ll take you through some use cases to help you see for yourself how the sum function can be applied.

Ready? Let’s begin!

What Does the SUMIF Google Sheets Function Do?

The SUMIF function is just an amalgamation of the SUM and IF functions. Its application is simple.

The function simply scans through a range of cells to find those that match a given condition.

When a match is found, the number corresponding to the cell is included in a group of selected numbers.

Once the function scans all the cells in the range, it takes the group of selected numbers and sums them up!

What Is the Difference Between SUMIF and SUMIFS?

Before we dive too deep into the SUMIF function, it’s important to distinguish the difference between SUMIF and SUMIFS.

SUMIF – is used when only one condition needs to be met for summing

SUMIFS – you need to use this if there’s more than one condition you want the formula to account for. Check out our detailed SUMIFS guide to learn more.

Related: How To Add in Google Sheets

Syntax of the SUMIF Function in Google Sheets

I always break down formulas into their parts. That helps me understand how to use them. Here’s my breakdown of the syntax of the SUMIF function:

=SUMIF(range, condition,[sum_range])

The component parts break down like this:

• Range is the group of cells that are tested for a condition.
• Condition is the criteria that a cell in range needs to fulfill to qualify as a match. The condition can be a value (number, text, date) or a reference to a cell containing the criteria.
• Sum_range is an optional parameter. If included, it is the range of cells containing values that will be added if its corresponding number in the range matches the condition. If the sum_range parameter is not included, then it is assumed that the range in the first parameter is also the sum_range.

As you can see from the above syntax, there are two ways to use the SUMIF function

• Without a separate sum_range
• With a separate sum_range

If all three parameters are given, then the SUMIF function checks each cell in the range to sum in order to see if it matches the condition.

If a cell does match the condition, then the SUMIF function takes the corresponding cell value in the range to sum and includes it in the final sum.

If just the first two parameters are given, then the SUMIF function goes through each cell in the range and adds only those cells that match the condition.

It finally returns the sum of all cells in the range that match the criteria.

Here’s what it looks like.

Google Sheets SUMIF Examples

The SUMIF function is such a versatile function that it can be used in a number of ways to accomplish a wide variety of tasks.

For example, you can use it to:

• Find the sum of only positive or negative numbers in a range
• Find the total sales for a department
• Find the total expenditure before a given date

These applications just scratch the surface. There are a number of other possibilities and areas where the SUMIF function Google Sheets can come in very handy.

However, for this article, let us just take a look at these three use cases. We will include another special use case when we explain wildcards.

Using SUMIF with Number Condition

The first use-case is to find the sum of only positive or negative numbers in a range.

Let us look at a dataset that consists of a combination of positive and negative integers, as shown below.

We want to add up only the positive numbers in the range A2:A10. Here’s how the SUMIF function can be applied in this case:

1. Select the cell where you want the result of the sum to appear ( C2 in our case ).
2. Type the following formula in the cell:

   =SUMIF(A2:A10,”>=0”)

   Notice that we did not include the third parameter in this case

3. Press the return key

This should display the sum of positive numbers in cell C2.

Explanation of the Formula

In this example, the SUMIF function checked each cell from A2 to A10 and selected only cells with values greater than or equal to 0.

It then added all the selected values and displayed the result in cell C2.

Note: If you want to display the sum of all negative numbers instead, all you need to do is change the condition to “<0”.

Using SUMIF with Text Criteria

Let us take a look at another example.

Below is a dataset where we want to sum up sales figures for the packaging materials department.

Below is how you can do this using the SUMIF formula:

1. Select the cell where you want the sum result to appear ( D2 in our case).
2. Type the following formula in the cell:

   =SUMIF(A2:A10,”Packaging”,B2:B10)

3. Press the return key

This should display the total sales of the Packaging department in cell D2.

Explanation of the SUMIF Formula in Google Sheets for This Example

In this example, the SUMIF formula Google Sheets checked each cell from A2 to A10 and looked for only those cells that contained the value “Packaging.”

For each cell that contains the word “Packaging,” the SUMIF function selected its corresponding sales value in column B. It then added all the selected values and displayed the result in cell D2.

Note: If you want to display the total sales for any other department, you can simply replace the condition in the second parameter with the required department name. Don’t forget to enclose the department name in double quotes.

Using a Google Sheets Conditional Sum With Date Conditions

Now, let us look at an example where we want to find the total expenditure before a given date, say 1 September 2019.

We will use the data set below to demonstrate how to use the SUMIF sheets function for this problem.

1. Select the cell where you want the result of the total expenditure to appear ( D2 in our case ).
2. Type the following formula in the cell:

   =SUMIF(A2:A10,”<”&DATE(2019,9,1),B2:B10).

3. Press the return key

This should display the total expenditure before DATE(2019,9,2), which just means 1 September 2019. We only used the DATE function to represent the date.

Note: We must concatenate the operator (“<”) to the date using an ampersand (&).

You can find the example sheets we have used in this article below.

Access Examples

Explanation of the Formula

In this example, the SUMIF function checked each cell from A2 to A10 and looked for only those cells that contained dates before 1 September 2019.

For each matching cell, the SUMIF function selected its corresponding expenditure value from column B.

It then added up all the selected expenditure values and displayed the result in cell D2.

There are a number of other ways in which you can use the SUMIF function.

Whenever you need to find the sum of values based on a condition, you can use this function by getting creative with the ‘condition’ part of the formula.

Using a SUMIFS Function With Logical Operators

Logic operators are used to compare values. They include:

Using the  Google spreadsheet SUMIF function with logical operators is fairly easy. For example, let’s say we wanted to find the sum for the expenditures that are less than 2000. We would use the formula:

=SUMIF(B2:B10,">2000")

However, if you wanted to find the total expenditure that was less than 2000 before the year 1 March 2022, then you would need to use the SUMIFS function. This is because SUMIF only allows a maximum of three arguments.

In this case, our formula would be:

=SUMIFS(B2:B10,B2:B10,">2000",A2:A10,"<1/3/2022")

You can also replace the values with the cell references holding the criteria. Just remember to enclose the logical operators with quotation marks. In this case, the new formula would be:

=SUMIFS(B2:B10,B2:B10,">2000",A2:A10,"<"A2)

How To Use SUMIF In Google Sheets With Wildcards

One creative way of using the SUMIF function is by incorporating wildcards into the condition part of the function.

For example, you have the following dataset containing quantities of different mobile phones in stock.

If you want to find the total quantity of all Samsung models, you can use wildcards like ‘*’ or ‘?’ in your SUMIF function as follows:

1. Select the cell where you want the result of the total sales to appear (D2 in our case).
2. Type the following formula in the cell:

   =SUMIF(A2:A10, “Samsung*”,B2:B10)

3. Press the return key

This should display the quantity of Samsung phones in cell D2.

Note: The asterisk wildcard character (*) is usually combined with a word or letter stem to find different variations of the term.

Explanation of the Formula

In this SUMIF Google Sheets example, the condition “Samsung*” means ‘find all cells that contain the word Samsung.’

It doesn’t have to be an exact match, but the cell should contain the word ‘Samsung’ and any other character(s).

Once a match is found, the SUMIF function takes the qty value corresponding to the matching cell and adds it to the list of selected quantity values.

Once it completes going through all the models, the SUMIF function sums up the selected quantity values and displays the result in cell C2.

You can use the ‘?’ wildcard in the same way. The ‘?’ wildcard represents a single character anywhere in the text string.

So if you wanted to search for, say, all Apple iPhone X models, you can use “Apple iPhone X?” in the condition.

How To Use Google Sheets SUMIF in Not Blank and Blank Cells

To do this, you use the same formula we’ve already been working with but with operators to define whether a cell is blank or not.

To SUMIF the criteria range is blank, you should use two quotations without a space in between like this “”

In the example above, we used the range C2:C6 with blank spaces as the range, “” (blank) as the criteria, and B2:B6 as the sum_range.

If you wanted to SUMIF the cells are NOT empty, you can use the not equal to operator “<>” without any numbers or words attached, like so:

Frequently Asked Questions

How Does the SUMIF Function Work in Google Sheets?

The syntax of the SUMIF function is as follows:

=SUMIF(range, condition,[sum_range])

You need to define the range that contains the condition first, then input the condition. Optionally, you can also add a third column of data that needs to be added together based on the condition and the first range.

How Do I SUMIF Multiple Columns In Google Sheets?

There is a trick to making this work. But the columns have to be side by side.

The trick is to repeat the range as many times as the number of columns you want to account for in the function and wrap it in {} brackets. Let’s take a look at an example:

Here, we wanted to get the SUM of John’s sales for Jan and Feb. So, that must be checked in two columns.

As you can see in the formula, we put the range in twice as {D2:D6, D2:D6}, then “John” as the criteria, and finally, the range to sum as B2:C6. That means that both columns will be used. You can do this for as many adjacent columns as you’d like.

Can the SUMIF Function Be Case Sensitive?

No, unfortunately, not on its own, at least. But, you could use an ARRAY Formula alongside a function that is case sensitive, like the FIND or EXACT functions.

Let’s take a look at an example:

In the above example, Product C and Product c are different items. If we used the normal SUMIF function, it would give the wrong result as it would add the two together.

We used the FIND function as its case sensitive and can find the exact term in the array. That needs to be wrapped in an ARRAYFORMULA to define the data highlighted in purple.

How Do I Use SUMIF and Not Equal to?

You just have to use the not equal to operator “<>” and a number or phrase as the criteria in your formula. Let’s take another look at our example and say we don’t want to include John’s sales in the SUM. In this case, we would put “<>John” as the criteria below.

Useful Tips for the SUMIF Function

When using SUMIF in Google Sheets, there are a few important points that you should keep in mind, so you can apply them to your data more effectively.

• If using a separate sum_range, remember to ensure that both the range and sum_range have an equal number of cells.
• The SUMIF function is not case-sensitive.
• If the condition contains a text value, date, or wildcards, then it must be enclosed in double quotes.
• Cell references in the condition are not enclosed in quotes.
• If the condition consists of a combination of an operator and a cell reference or another function (for example, the DATE function), then they must be joined together by an ampersand (&). For example,
  • “<”&DATE(2019,9.1)
  • “>”&B2
• You can only use a maximum of three arguments in the SUMIF formula. This means that you can only apply one condition in the formula. If you want to use more than one condition, then you will need to use the SUMIFS function instead.
• You cannot substitute the range and sum_range references with arrays.
• When using the filling function, make sure to lock the ranges
• You can’t substitute the ranges for an array in the sum formula.

Conclusion

In this tutorial, we showed you how to use the SUMIF Google Sheets function. It’s used in Google Sheets to add value based on another cell. This is a very versatile function that has a wide variety of applications.

We showed you how to use the function in different use cases, and we encourage you to apply the SUMIF function whenever you need to sum up numbers based on a condition.

Other Google Sheets tutorials you may like:

• • How To Sum a Column in Google Sheets
  • COUNTIF and COUNTIFS Functions
  • How To VLOOKUP from Another Sheet in Google Sheets?
  • How To Use the FILTER Function in Google Sheets
  • IFS Function in Google Sheets – Test Multiple Conditions
  • How To Use the INDIRECT function in Google Sheets