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.
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.
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.
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 SheetTo Import the example datasheet into your spreadsheet,
Let’s continue all our particle topics based on the above example datasheet.
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.
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 ).
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. 
=SUMIF(
'Sportswear Sales inventory'!C2:C1001,
"*Reebok*",
'Sportswear Sales inventory'!F2:F1001
)
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.
=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.
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.
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.
Google Sheet, Spread Sheet As a data analyzer every time you deal with unstructured data for different purposes which stakeholder needs. Sometimes you have to
Google Sheet, Spread Sheet Google sheets extremely powerful that built with over 200+ reusable functions and shortcuts. It allows to create table and fill data
Google Sheet, Spread Sheet Google sheets offer various formulas to implement with your data manipulation. Many times you have to implement checkboxes in google sheets.
Google Sheet, Spread Sheet Google sheets are now becoming smarter and it can be used for operating with a collaborative team with a large data
