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

Summing up a range of numbers may be quite easy for even a beginner to Google Sheets.

However, when it comes to summing up numbers based on a condition, that’s when things start to get tricky. Thankfully, you can use the convenient SUMIF Google Sheets function to help 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, we will help you understand the basic syntax for the Google Sheets SUMIF function and take you through some use-cases to help you see for yourself how the sum function can be applied.

You may also take a course similar to Spreadsheet Fundamentals by DataCamp to learn other functions.

Let’s begin!

## What does the SUMIF Google Sheets Function Do?

The SUMIF function is basically 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 is done scanning through 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 that you want the formula to account for. Check out our detailed SUMIFS guide to learn more.

## Syntax of the SUMIF Function in Google Sheets

The syntax of the SUMIF function is as follows:

`=SUMIF(range, condition,[sum_range])`

Here,

• 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 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 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.

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 those cells that have values greater than or equal to 0.

It then added up 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 we have a dataset where we want to sum up sales figures for the packaging materials department.

Below is how you can do this using SUMIF formula:

1. Select the cell where you want the result of the sum 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 contain 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 up 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 to the department name you need. 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 shown 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 here.

Explanation of the Formula

In this example, the SUMIF function checked each cell from A2 to A10 and looked for only those cells that contain 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, just 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:

 = Equal to <> Not equal to > Greater than < Less than <= Greater than or equal to >= Less than or equal to

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, say 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’, along with 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 is used to represent 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 which has blank spaces in it 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:

## SUMIF in Google Sheets FAQ

### How Does the SUMIF Function Work in Google Sheets?

The syntax of the SUMIF function is:

`=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 needs to 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 a 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 like 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 it to your data more effectively.

• If using a separate sum_range, remember to make sure 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 you can 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:

#### Most Popular Posts #### Sumit

Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.

### 7 thoughts on “How to Use the SUMIF Google Sheets Function (3 Easy Examples)”

1. Thank you so very much! This worked like a charm. Saved me hours of racking my brains to figure out what to do.

2. Hi,

This is super super helpful, how do I sum the contents for a week range, for example, give me the total units for the week of Monday July 19 to Friday July 23?

3. Just a comment, I repeated got a parsing error, finally I found out that the solution was quite simple: replacing the comma with a ; did the trick. So somehow Sheets doesn’t work (anymore?) with commas?

4. If I have several columns that when added across equal less than 250.00 how do I set the sum total to be 250.00. If it goes over 250.00 I would just need the sums added across the columns.

5. The trick under title “How Do I SUMIF Multiple Columns In Google Sheets?” doesn’t work for me. I have found the solution which works and does the same:
=sum(SUMIF(D2:D6;”John”;B2:B6);SUMIF(D2:D6;”John”;C2:C6))

6. Under title “Can the SUMIF Function Be Case Sensitive?” the formula has redundant brackets. Instead of
=SUMIF(ARRAYFORMULA((FIND(“Product c”;A2:A6));1;B2:B6) should be
=SUMIF(ARRAYFORMULA(FIND(“Product c”;A2:A6);1;B2:B6)

• Thanks, Anna. You’re right