# How to Use the SUMIF Function in Google Sheets [With Examples]

Google Sheets offers a range of functions to perform various complex calculations. The SUMIF is the conditional function in Google Sheets to add values across a range of cells. You can access this formula by going to the main menu and clicking the “Insert” tab followed by selecting the “Function“.

The function lets users add numbers based on criteria defined by them.

Sometimes, it becomes really hard to analyze complex datasets to prepare reports.

But no worries because spreadsheet programs like Google Sheets come with powerful functions and tools to make your life easier.

In this article, let us dive deep into the SUMIF function and learn to find the sum of a range of cells that satisfies a given condition. We will discuss the syntax and a few examples with step-by-step instructions, so make sure to read the article till the end.

The SUMIF function can be used for text strings, numbers, and even dates. Click on the following links to jump to the particular section of your interest:

The above Google Sheet contains several spreadsheets with the datasets used to demonstrate various SUMIF examples discussed in the following sections.

Practice is crucial to master the SUMIF function!

## Explained: The SUMIF Function in Google Sheets

Simply put, the SUMIF function is useful in cases where users wish to add numbers from the cells that meet specific criteria.

It is a simple function that both beginners and experts can use.

The general syntax for the SUMIF function is as follows,

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

Each argument of the SUMIF function needs to be replaced with the proper information.

• range” – It represents the range of cells you wish to sum. The range defined here will be tested against the criteria defined by the users, and the values from the cells that meet the criteria are added.
• criteria” – Here, you can define the criteria, also known as the condition. It will be tested against the cell range to determine cells to sum up. The criteria may include anything from matching text strings, finding numbers greater than a specific number, getting dates that fall between a given month, etc.
• sum_range” – It is an optional argument. If you keep it blank, the function will add the numbers from the “range” argument where you have defined a cell range. In simple words, this argument needs to be replaced with the secondary range of cells you wish to sum if the respective cells defined in the “range” argument meet the condition defined in the “criteria” argument.

The “sum_range” argument is quite useful in cases such as finding the sales generated by a specific person over time.

The syntax of the SUMIF function is pretty straightforward.

## SUMIF Function Examples – Google Sheets

We will consider a range of scenarios, including text strings, numbers, and dates.

Note that using the SUMIF function is quite simple, but it largely depends upon the scenario you deal with.

Let us discuss a few typical scenarios that would strengthen your knowledge of the SUMIF function and prepare you for future complex data analysis.

### How to use the SUMIF function to add up numbers with the Text String as a criteria

In this particular section, we will consider the examples that include a text string within the criteria.

Let’s begin!

#### Example #1 – Find the sum of the cells that contain a specific text string

Consider the following image,

We have employee data with their annual salary. Let us find the total amount spent on the salary of the IT department over a year.

Here are the steps,

• Select the desired cell
• Type “=sumif
• Select the first option from the popup or press “Tab” key
• Now, as we are interested in finding the amount spent on the salary of the IT department, the “range” argument will be replaced with the “B2:B20
• Press “,” to move to the next argument
• For the “criteria” argument, let us type “IT”
(Make sure to put the keyword “IT” within double quotation marks.)
• Press “,” key
• Provide the cell range “C2:C20” for the “sum_range” argument
• Complete the parentheses using “)
• Press “Enter

Our final formula after following the above steps is as follows,

``=SUMIF(B2:B20,"IT",C2:C20)``

Google Sheets will instantly display the total amount spent on the IT department over the year as salary.

#### Example #2 – Find the sum of the cells that do not contain a specific text string

Let us use the same dataset discussed in Example #1.

Here, we will exclude the amount spent on the salary of the IT department from the total salary over the year.

In other words, we need to find the sum of the cells that do not contain the keyword “IT”.

For this purpose, we need to use a Comparison Operator in Google Sheets. There are a total of six comparison operators as follows,

• =” – Equal To
• ” – Less Than
• ” – Less Than or Equal To
• >” – Greater Than
• >=” – Greater Than or Equal To
• ” – Not Equal To

We’ll use the Not Equal To (“”) operator to exclude the IT department from our calculation.

Here are the steps,

• Select the desired cell
• Type “=sumif
• Select the first option from the popup or press “Tab”
• Provide the cell reference as “B2:B20” for the “range” argument
• Press “,” to move to the next argument
• Now, using the Not Equal To operator, let us type “IT” and make sure to use the double quotation marks
• Press “,” key
• Provide the cell reference as “C2:C20” for the “sum_range” argument
• Close the bracket using “)”
• Press “Enter

Our final formula after following the above steps is as follows,

``=SUMIF(B2:B20,"IT",C2:C20)``

Note that most of the Comparison Operators are useful in the case of the Numbers, whereas the Equal and Not Equal To operators are used for both text strings and numbers.

#### Example #3 – Find the sum of the cells that contain a portion of the text string

Let us consider the following dataset,

We have fruits in one column and their prices in the second column.

Our job is to find the sum of the cells that contain the “Apple” keyword.

For this purpose, we will use Wildcard Characters in Google Sheets. There are two types of Wildcard Characters:

• ?” – The question mark wildcard lets users find the cells that contain a single character
• *” – The asterisk wildcard lets users find the cells that contain a sequence of characters

As the keyword “Apple” is made up of a sequence of characters, we need to use the Asterisk “*” wildcard.

Here are the steps,

• Select the desired cell
• Type “=sumif
• Select the first option from the popup or press “Tab” key
• Provide the cell reference “A2:A10” for the “range” argument
• Press “,
• Now, using the Asterisk wildcard character type “*Apple” and make sure to use the double quotation marks
• Press “,
• Provide the cell reference “B2:B10” for the “sum_range” argument
• Close the bracket using “)
• Press “Enter” key

Our final formula will be as follows,

``=SUMIF(A2:A10,"*Apple",B2:B10)``

Note that if you choose to define the criteria using the cell reference, then make sure to put the Asterisk wildcard character in double quotation marks and use the “&” symbol to join the cell reference value.

Consider that we need to use the cell reference “D2”, which holds the keyword “Apple”. Our formula will be as follows,

``=SUMIF(A2:A10,"*"&D2,B2:B10)``

### How to use the SUMIF function to add up numbers with the Numbers as criteria

As mentioned earlier, to find the sum of numbers that meets a given criteria, we need to use the Comparison Operators in Google Sheets.

There are a total of six of them, as follows:

• =” – Equal To
• ” – Less Than
• ” – Less Than or Equal To
• >” – Greater Than
• >=” – Greater Than or Equal To
• ” – Not Equal To

They are self-explanatory from their name itself, and you won’t face any difficulty choosing them.

However, you need to be careful while inserting them within the formula and make sure to use double quotation marks. Otherwise, the function will return an error.

#### Example #4 – Find the sum of the cells that are equal to the given number

This is a simple scenario that uses the Equal To comparison operator.

Here’s the example dataset where we have product names in the first column, items sold in the second column, and unit prices in the third column.

Let’s find the total price of the products whose 15 units have been sold over time.

Note that for the sake of this article, we are simply calculating the total price of the product and not the revenue generated by selling each product.

Select the desired cell

• Type “=sumif
• Click on the first option from the popup or press the “Tab” key
• Provide the cell reference “B2:B10
• Press “,
• Now, we will use the Equal To comparison operator and type “=15”
(Make sure to use double quotation marks to avoid errors at the end.)
• Press “,
• Provide the cell reference “C2:C10” for the “sum_range” argument
• Complete the bracket using “)
• Press the “Enter” key

Our final formula will be as follows,

``=SUMIF(B2:B10,"=15",C2:C10)``

As 15 units of the Orange, Watermelon, and Green Apple are sold, and their prices are \$61, \$56, and \$95, the total sum of their prices is \$212.

#### Example #5 – Find the sum of the cells that are greater than the given number

Let’s find the salary of the employees who have been working for more than two years for a company. Refer to the following image.

It contains the total number of years in column B and the annual salary in column C.

• Select the desired cell
• Type “=sumif
• Select the first option from the popup or press “Tab” key
• Provide the cell reference “B2:B10” for the “range” argument
• Press “,” to move to the next argument
• Now, using the Greater Than comparison operator type “>2”
(Make sure to use double quotation marks; otherwise, the formula will return an error at the end.)
• Press “,
• Provide the cell reference “C2:C10” for the “sum_range” argument
• Complete the bracket using “)
• Press “Enter” key

Here’s how the formula looks after following the above steps,

``=SUMIF(B2:B10,">2",C2:C10)``

Also, in the case of the cell reference for the criteria argument, make sure to use the double quotation marks for the Comparison Operator and the “&” symbol to join the cell reference value with it.

The formula with the cell reference “D1”, for example, will be as below,

``=SUMIF(B2:B10,">"&D1,C2:C10)``

#### Example #6 – Find the sum of the cells that are greater than or equal to the given number

Consider the previous dataset for this example as well.

We need to find the salary of the employees who have been working for the company for the last 1 year and more than that.

The steps are similar, and you only need to use the Greater Than or Equal To operator.

Let’s begin,

• Select the desired cell
• Type “=sumif
• Choose the first option from the popup or press “Tab”
• Provide the cell reference “B2:B10” for the “range” argument
• Press “,
• Next, using the Greater Than or Equal To comparison operator, type “>=1
(Make sure to use double quotation marks; otherwise, the formula won’t work at the end.)
• Press “,
• Replace the “sum_range” argument with the cell reference “C2:C10
• Complete the parentheses using “)
• Press “Enter

Here’s the final formula using the steps above,

``=SUMIF(B2:B10,">=1",C2:C10)``

In the case of using the cell reference for the “criteria” argument, you must use the double quotation marks for the comparison operator and the “&” symbol to join the cell reference value with it, as discussed at the end of the previous example.

### How to use the SUMIF function to add up numbers with the Dates as a criteria

To find the sum of the cells that satisfy the given date criteria using the SUMIF function, you need to use the Comparison Operators discussed above.

#### Example #7 – Find the sum of the cells that are equal to a specific date

For this problem, the Equal To comparison operator is your solution.

Let’s look at the steps to add the cells that contain the values equal to a given date.

Consider the above table, where we need to find the total sales on January 13th, 2023.

• Select the desired cell
• Type “=sumif
• Select the first option from the popup or press “Tab”
• Provide the cell reference as “A2:A20
• Press “,” to move to the next argument
• Now, use the equal operator and replace the “criteria” argument with the “=1/13/2023”
(You must enter the date in one of the acceptable formats for Google Sheets to interpret as a date)
• Press “,
• Provide the cell reference “B2:B20” for the “sum_range” argument
• Complete the bracket using the “)
• Press “Enter

Here’s how our final formula looks,

``=SUMIF(A2:A20,"=1/13/2023",B2:B20)``

#### Example #8 – Find the sum of the cells that contain a date before a specific date

Let us use the same dataset used in the previous example.

Our task is to find the sales before January 15th, 2023.

We will use the Less Than or Equal To operator to find the sales before the given date.

• Select the desired cell
• Type “=sumif
• Select the first option from the popup
• Provide the cell reference “A2:A20
• Press “,
• Next, we will use the Less Than or Equal To comparison operator to find the total sales before January 15th, 2023.
• Type “
(Make sure to use double quotation marks and enter the date in a predefined format.)
• Press “,
• Provide the cell reference “B2:B20
• Press “Enter

Your final formula after following the above steps should be as follows,

## Things you need to know before using the SUMIF function in Google Sheets

For the SUMIF function to work properly without any errors, make sure you understand the following things:

### 1. The range defined in the “range” and “sum_range” criteria should be equal

Google Sheets won’t return any error if the cell range defined in the “range” and “sum_range” arguments are different.

But note that it may mess up your final results.

For example, let’s assume we wish to find the sales generated by the John Doe, and our formula is as follows,

``=SUMIF(A2:A10,"Jon Doe",B2:B10)``

In the above formula, the “A2:A10” is the cell range against which the criteria “John Doe” will be tested and “B2:B20” represents the cell range to sum up.

Refer to the image above, where we have found that the sales generated by John Doe is “\$143,603”.

Now, let’s add few more entries as shown below,

Also, let us change the sum range from “B2:B10” to “B2:B20” and keep the range “A2:A10” as is in our formula.

``=SUMIF(A2:A10,"John Doe",B2:B20)``

So, as the actual sum range has been increased, as shown in the above formula, the sum should be greater than the earlier. But look at the results using the above formula,

It hasn’t changed!

This is due to the fact that we haven’t increased the range from “A2:A10” to “A2:A20”. In other words, the criteria will be tested against the cells that fall between A2 and A10.

So, the results remain the same!

Now, if you change the formula and increase both the range and sum range, it should look like the below,

``=SUMIF(A2:A20,"John Doe",B2:B20)``

The results will change to “\$258,458”, as shown in the following image.

### 2. The criteria needs to be put in double quotation marks

While defining the criteria within the SUMIF function, make sure to use double quotation marks.

It applies to everything from text strings to dates and even while using logical operators and wildcard characters.

The function will return a “Formula parse error.” message if you don’t use the double quotation marks. Refer to the following image.

### 3. The “criteria” argument of the SUMIF function is not case-sensitive

The SUMIF function doesn’t differentiate between lowercase and uppercase letters.

For example, the criteria including the “John Doe” and “john doe” are both the same.

The function will only consider the characters regardless of whether they are in lowercase or uppercase while adding the numbers.

You can combine the SUMIF with other functions in Google Sheets to create a case-sensitive formula.

### 4. Only one criterion or condition can be tested using the SUMIF function

As per the syntax of the SUMIF function, it allows users to test only one criterion against the given cell range.

Simply put, the SUMIF function is used to define a single condition and evaluate it against a single cell range.

For testing multiple criteria against multiple cell ranges, you need to use the SUMIFS function.

Here’s the syntax of the SUMIFS function,

``=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)``

It lets you define multiple criteria and cell ranges (criteria_range1, criteria_range2, criteria_range3, etc.)

That’s it!

## FAQs

### Q. How do I find the sum of the cells that are blank?

In this case, you need to use the Equal To comparison operator.

Consider the following example,

The blanks in column B represent the products whose zero quantities have been sold.

To add up the price of the products that didn’t make any sales over time, your formula will be as follows,

``=SUMIF(B2:B10,"=",C2:C10)``

Here, we have used “=” (Equal To) to find the blank cells within column B.

## Conclusion

You have made it to the end. Now, you know everything about the SUMIF function in Google Sheets.

The function is quite simple but practice will help you master it.

Feel free to comment below if you are stuck somewhere while using the SUMIF function.

Also, don’t forget to explore our blog section for more Google Sheets tips and tricks.