How To Use SUMIF In Google Sheets ( 5 useful examples)

When you deal with interrelated data sets in an organization, you have to reformat and summarize data to your stakeholders to make a decision to achieve their goals. Sometimes you have to calculate and get the total set of riven ranges everyone knows the SUM formula in google sheets can be easily integrated with that perspective. But sometimes stockholders request conditional base sum values in a given range to summarize and categorize data. Instead of the classic IF Function, the google team introduced a special formula called SUMIF. The SUMIF in google sheets is used to manipulate conditional base operation and return total (sum) in a given range.

This is the basic step to create SUMIF in google sheet.

Open a spreadsheet in Google Sheets.
Identify data range which you need to test against criterion
Select the cell which you want to return an argument return value.
Then Type =SUMIF(insert column range which needs to test against criterion, criterion, [calculation total (SUM)] of selected range)

About This Article
In this article describe features of SUMIF in google sheets and usage with different user requirements with example and how to implement different condition operation.

Contents

1 Syntax and features of SUMIF in google sheets
- - 1.0.1 About example
2 How to use the regular SUMIF in google sheets
3 Partial match (wildcard filter) with SUMIF in google sheets
4 Logical operator (AND, OR) with SUMIF in google sheets
5 Error handling with SUMIF in google sheets
- - 5.0.1 Conclusion

Syntax and features of SUMIF in google sheets

The SUMIF in google sheets mainly designs for calculating total in a given range which is based on a criterion. This formula uses the following syntax and features.

Range: This is a required value that represents the column range that tests against the criterion. Make sure that if the range contains date or currency base data, convert in the same format as the execute argument.
Criterion: this is a requirement that carries out an argument against a given range.
Sum_range: this is an optional value that represents a calculator given range which is based on criteria in SUMIF operation. Make sure that your sum range value is numeric because this is to calculate the total (sum) in operation otherwise it will return zero(0).

SUMIF in google sheets only performs with a single criterion. You have to use ARRAYFORMULA or SUMIFS for logical operations such as AND / OR / NOT.

Still don’t you have any idea about how to use If Function in google sheets, we highly recommend reading Right Way To Use Google Sheets IF Function ( With 6 Helpful Tips ) Article.

About example

Let’s take shoe sales inventory in a sportswear showroom. This store company was started in 2018, they sell 6 different types of sport shoe brands(Adidas, Fila, New Balance Nike etc…, ), also working 5 well-experienced sales rep persons. their customers visit the showroom & buy via online websites too. Both of the above order types are handled by sales reps. Showroom owner maintains a google spreadsheet as inventory to summarize all sales, its name as Sportswear Sales inventory This datasheet contains sales records from 2018. Also, he uses another tab for previewing all Summary of Inventory.

Download Sample Sheet

To Import the example datasheet into your spreadsheet,

Open a spreadsheet in Google Sheets.
On the top of navigation click File > Import (import datasheet)

Let’s continue all our particle topics based on the above example datasheet.

How to use the regular SUMIF in google sheets

This is the basic usage of SUMIF with a given data range in the datasheet. Let’s continue with our sportswear inventory.

(base on shoe sales inventory datasheet)
The shop owner needs to calculate the total sale of Adidas shoes. Let’s check how to do this with the SUMIF in google sheets.

Open a spreadsheet in Google Sheets.
Base on the example I need to get the “Adidas” brand from the Brand column (column C2 to C1001) and calculate the sum in the Total column (column F2 to F1001) in the Sportswear Sales inventory tab
Select the cell which you want to return an argument return value ( i selected B5 in the Summary of Inventory tab ).
Then type =SUMIF(‘Sportswear Sales inventory’!C2:C1001,“Adidas”,‘Sportswear Sales inventory’!F2:F1001) in B5 cell in Summary inventory sheet.
NOTE: if you need to implement dynamic cell range please update your formula like the below image
Then the total sale of the “Adidas” brand will return on B5 in the summary of inventory

This basic usage of SUMIF in google sheets. Additionally, I updated the B5 cell as a currency operation cell to get a better preview (Select Cell > select Format in top navigation > Number > Currency ).

Partial match (wildcard filter) with SUMIF in google sheets

In this situation, we cannot measure our data in 100% correctly & it can be completely used for analysis. Your datasheet also contains unstructured and you have to reorganize for stakeholder perspective.

Sometimes you have to partially match text, number, or symbols to the group to organize single or multiple other characters in formulas. In google sheets, it is called a wildcard filter. This is really important to prevent reorganizing the same value for multiple purposes.

Let’s check with an example.

The shop owner identified that in the brand column, there are two types of Reebok brand (Reebok and Reebok 2.0, 2.20 Reebok) actually it’s the same brand but the data operator made a mistake. Anyhow, shop owners need to get the total of both Reebok and Reebok 2.0 in the datasheet.

Actually to solve this issue you can get SUMIF of Reebok and Reebok 2.0 and 2.20 Reebok as separate formula and calculate as single result.

=(
	SUMIF('Sportswear Sales inventory'!C2:C1001,"Reebok",'Sportswear Sales inventory'!F2:F1001) +
	SUMIF('Sportswear Sales inventory'!C2:C1001,"Reebok 2.0",'Sportswear Sales inventory'!F2:F1001) +
	SUMIF('Sportswear Sales inventory'!C2:C1001,"2.0 Reebok",'Sportswear Sales inventory'!F2:F1001)
)

But particularly this is not a standard way. This is an unnecessary query and hard to maintain. Instead of this method, google sheets already introduce a wildcard filter. We can use the Asterisk symbol (*) to match any sequence of characters in the given criterion.

Based on our client’s problem we have to check both sides of the characters in Reebok.

Select the cell which you want to return an argument return value (i selected B7 in the Summary of Inventory tab)
Then type =SUMIF(‘Sportswear Sales inventory’!C2:C1001,”*Reebok*”,’Sportswear Sales inventory’!F2:F1001)

=SUMIF(
    'Sportswear Sales inventory'!C2:C1001,
    "*Reebok*",
    'Sportswear Sales inventory'!F2:F1001
)

Logical operator (AND, OR) with SUMIF in google sheets

In the SUMIF formula section I already mentioned, the SUMIF criterion only executes a single argument. You have to use ARRAYFORMULA and SUMIF together or SUMIFS instead of that. And also you can combine the SUMIF formula together in cell.

How to do with OR operator,

In Conditional Statement, OR logical operator will execute at least one criterion is TRUE. this same thing actually operates by SUMIF, it also returns the value if the criterion is TRUE. when combining multiple SUMIF formulas like this (SUMIF() + SUMIF() + SUMIF()) it will only emit a total of TRUE value .

How to do with AND operator,

If you need to compare multiple criteria in the same statement, you have to use AND logical operator. Will return data if all the criteria are TRUE. If you are already familiar with IF Function in google sheet, AND operator can be used to this kind of situation. you can use 2 way to handle multiple criterion SUMIF formula in google sheets.

Use ARRAYFORMULA function in google sheets
ARRAYFORMULA can operate multiple calculations on one or more items in an array and it will return a range of cells instead of a single value. Using the SUM formula in google sheets, we can calculate the total in ARRAYFORMULA return.

Let’s check with an example,

The shop owner needs to calculate the total sale of Adidas & Nike brands. Let’s check how to do this with ARRAYFORMULA in google sheets.

Select the cell which you want to return an argument return value ( i selected B8 in the Summary of Inventory tab )
Then type =ARRAYFORMULA(SUM(SUMIF(‘Sportswear Sales inventory’!C2:C1001,{“Adidas”, “Fila”},’Sportswear Sales inventory’!F2:F1001)))

=ARRAYFORMULA(
	SUM(
		SUMIF(
		'Sportswear Sales inventory'!C2:C1001,
		{"Adidas", "Fila"},
		'Sportswear Sales inventory'!F2:F1001
		)
	)
)

Use SUMIFS function in google sheets

To prevent query barriers, google sheets introduce SUMIFS formulas to implement multiple criteria and return a single value into cell

Still don’t have any idea about logical operators in google sheets, we highly recommend reading Right Way To Use Google Sheets IF Function ( With 6 Helpful Tips ) Article.

Error handling with SUMIF in google sheets

We all know we have to deal with many types of data which are collaborated by many people and many purposes, may by your existing data not being consistent for another purpose then google sheet will return a number corresponding to the error value in a different cell instead of the expected result. Our stakeholders don’t like errors. so we have to handle it properly with given conditions in google sheets.

We can use the IFERROR formula in google sheets to overcome unwanted errors & replace the meaningful value on a cell in google sheets.

Still don’t have any idea about Error handling formulas in google sheets, we highly recommend reading the Top 7 Google Sheets Formulas Every Beginner Should Know Article.

Let’s check how to implement with SUMIF formula in google sheets

=IFERROR(
	SUMIF(
		'Sportswear Sales inventory'!C2:C1001,
		"Adidas",
		'Sportswear Sales inventory'!F2:F1001
	),
  "please check your syntax and cell values"
)

This is really helpful to overcome error messages emitted at the end-user level. But I highly recommend double-check your statement and data set of the given range before IFERROR implement.

That’s it. I hope this article will help to get a basic idea of how to use SUMIF in google sheets and how to implement real-world situations with examples.

Conclusion

Google sheets is an extremely super functional and totally free cloud base application. It is really popular now rather than ms excel. Google sheets developer team will introduce new features to the community to make their work doing without any hassle. SUMIF in google sheets is one of the popular conditional operations. It can operate conditional base total calculation.