Google Sheets boasts powerful functions (aka Formulas) to perform complex calculations. The SUMIFS function finds the sum of cell values when multiple conditions are satisfied. To access this function, go to the “Insert” tab of the main menu and click “Function.”
The function only adds numbers from a range when all conditions are met.
In other words, you can define multiple conditions or criteria that are tested against a range, and then each cell that satisfies all of those conditions is added to find the sum.
This is a quick guide about the SUMIFS in Google Sheets. Let us dive deep into the syntax and use cases of this function. We are about to discuss various SUMIFS examples, so read the article until the end.
Download the Example Google Sheets
Before we proceed further in this article, make sure to download the following Google Sheet, which contains the dataset used to demonstrate the SUMIFS function, and follow me along as we move down the article.
It is okay if you have your own Google Sheet ready with the required dataset; skip downloading the above file.
But please note that practice is the key to success here.
Why use the Google Sheets SUMIFS Function?
Consider an example where we have a dataset of monthly e-commerce sales. Now, let’s assume that our task is to find the total sales generated by the Green Apples between January and April.
In this case, the SUMIFS function is helpful.
It allows you to define multiple criteria as finding the sum of the cells that contain,
- The name of the given product
- The months between January and April
When all of these conditions are met, the function adds up the corresponding cell values to find the product sales between January and April.
Explained: The SUMIFS Function in Google Sheets
The SUMIFS is a conditional function in Google Sheets. It is a simple formula that lets users find the sum of the cells that satisfies the given criteria.
The general syntax is as follows,
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])Each argument of the function needs to be replaced with the proper information.
- “sum_range” – It represents the cell range that will be added to find the sum if the conditions or criteria defined by the users are satisfied.
- “cirteria_range1” – It represents the cell range that will be tested against the criteria defined by the users. If the condition or criteria is satisfied by the given cells of this range, the corresponding cells from the range specified within the sum_range are added to find the sum.
- “criterion1” – Here, users can define their first criteria. The cirteria_range1 argument needs to satisfy this criteria. It includes logical expressions with numbers, letters, and dates.
- “criteria_range2” – This is the next cell range, which will be tested against the second criteria defined by the users. It is an optional argument.
- “criterion2” – It is also an optional argument where you can define the second criteria to test against the cell range provided in place of the criteria_range2 argument.
You can define any number of criteria. There is no limit! But make sure to specify at least one criterion for this function to work.
For the function’s criteria arguments, you can manually enter the values or provide the cell references holding the values.
Additionally, note that the Metacharacters and Comparison Operators are used to define the criteria.
Here is the list of Comparison Operators that can be used within the Google Sheets:
- “=” – Equal To
- “<“- Less Than
- “<=” – Less Than or Equal To
- “>” – Greater Than
- “>=” – Greater Than or Equal To
- “<>” – Not Equal To
All of them are self-explanatory. We will discuss more about them, along with the examples, in the upcoming sections of this article.
3 Things to know before using the SUMIFS function in Google Sheets
Using the SUMIFS function is pretty simple.
However, there are a few things to remember to avoid misleading calculations and errors after using this formula.
In this section, let us discuss people’s most common mistakes while using the SUMIFS function.
1. Equal range should be defined within the arguments “sum_range” and “criteria_range”
You must ensure the range defined within the sum_range and criteria_range arguments is equal to avoid the “#VALUE!” error. Refer to the following image.
The function has returned the error, “Array arguments to SUMIFS are of different size.”
It is due to the fact that the sum_range is “C2:C21” and criteria_range is “A2:A25”. Ideally, the criteria_range should be “A2:A21”.
After replacing the same with the main formula, the function works correctly, as shown in the following image.
2. Cells are summed only when all the criteria are met
In the case of multiple criteria or conditions, the function finds the sum of cell values only after all the criteria are met.
Consider the following example,
Suppose that you have defined two criteria,
- The name should contain the “Green Apple” keyword
- The sale should be more than “$1000”
Our formula is as follows. It is using the range “C2:C21” as the sum_range.
=SUMIFS(C2:C21,A2:A21,"Green Apple",C2:C21,">1000")Now, the function is set to find the sum of monthly sales of Green Apples when it exceeded the mark of $1000.
So, in short, we can say that the working of the SUMIFS function is similar to the AND logical operator in programming.
3. The letters, numbers, and special characters should be enclosed within double quotation marks
Make sure to put the following things in double quotation marks while using the SUMIFS function,
- Letters
- Numbers
- Dates
- Comparison Operators
- Metacharacters
Besides cell reference values, nearly everything used within the formula must be enclosed within the double quotation marks. Refer to the following image where I have put greater than (>) comparison operator and number (1000) within double quotation marks.
How to use the SUMIFS function in Google Sheets (AND Logic)
You don’t need expert knowledge to use the SUMIFS function in Google Sheets. It all boils down to your understanding and knowledge of the logic or criteria that need to be used.
We will discuss different examples and learn to combine the SUMIFS with other functions using the OR logic.
But let’s start with the basics.
- SUMIFS with Text Criteria
- SUMIFS with Number Criteria
- SUMIFS with Date Criteria
Example #1 – Using the SUMIFS in Google Sheets with the Text Strings as criteria
It is a simple scenario where the logical expressions include the text strings.
Consider the following example,
Here, we have employee names in the first column, city in the second, department in the third, and their salary in the fourth.
Now, let us find the salary of the employees from Phoenix working in the IT department.
Let’s start,
- Select the desired cell
- Type “=sumifs”
- Choose the first option from the popup or press the “Tab” key
- Replace the “sum_range” argument with the “D2:D20”
- Press “,” to move to the next argument
- Replace the “criteria_range” with the cell range “B2:B20“
- Press “,“
- Now, our first criterion is the employees from Phoenix. So, we will replace the “criteria1” argument with the “Phoenix“
(Make sure to use double quotation marks; otherwise, the formula won’t work at the end) - Press “,“
- For the second criteria range, we will put “C2:C20“
- Our second criterion is people working in the IT department, so let us type “IT“
(Make sure to use the double quotation marks) - Complete the bracket using “)” on your keyboard
- Press “Enter” key
Our final formula is as follows,
=SUMIFS(D2:D20,B2:B20,"Phoenix",C2:C20,"IT")The function has instantly calculated the annual salary of the employees from Phoenix working in the company’s IT department as $382,885.
Make sure to select the sum_range and criteria_range cell references correctly. They should be equal; otherwise, the formula will return a “#VALUE!” error.
Example #2 – Using the SUMIFS in Google Sheets with the Numbers as criteria
It is another common scenario where you need to create logical expressions using numbers.
Here, we need to use the Comparison Operators that are discussed at the start of this article. There are a total of six of them, but we will use equal to (=), less than (<), and greater than (>) comparison operators.
Here’s an example dataset,
Our task is to find the total sales generated by the employees who have recently joined in the last 2 years but received a bonus greater than $10,000.
Here are the steps,
- Click the desired cell
- Type “=sumifs”
- Select the first option from the popup or press “Tab” on your keyboard
- The sales generated by the employees are in column D. So, let us replace the “sum_range” argument with the “D2:D11“
- Now, press “,” to move to the next argument
- Total employment years are mentioned in column B. We will replace the “criteria_range1” argument with the range “B2:B11“
- Press “,“
- For the first criterion, we will type “<=2“
(Note that the “<=” symbol before the number 2 tells the function to consider the cells that contain a number less than or equal to 2) - Press “,“
- Next, for the “criteria_range2” argument, we will provide the cell range “C2:C11” as column C holds the bonus received by each employee
- Press “,“
- For the second criterion, we will replace the “criteria2” argument with the “>10000”
(Make sure to use the double quotation marks) - Complete the bracket using “)“
- Press “Enter” on your keyboard
After following the above steps, here’s what our formula looks like.
=SUMIFS(D2:D11,B2:B11,"<=2",C2:C11,">10000")As per the above GIF, the total sales generated by the employees who have joined during the past 2 years but have received the higher bonus is $5,483,582.
You can use the SUMIFS function for various scenarios, including finding the total sales of the products over the last 3 months with a price tag below $100.
Example #3 – Using the SUMIFS in Google Sheets with the Dates as criteria
In this example, we will use multiple criteria with dates and numbers. Consider the following dataset: product sales data with their price and quantity sold between January and March.
Our task is to find the total sales in January by the products with more than 5 quantities. In short, there are two criteria,
- January month
- Quantities sold above 5
We need to use the comparison operators for this example as well. Here are the steps:
- Click on the desired cell
- Type “=sumifs”
- Select the first option from the popup or press “Tab” key
- For the first argument named “sum_range”, let us provide the cell range as “E2:E21“
- Now, press “,” on your keyboard to move to the next argument of the SUMIFS function
- Provide the cell reference as “E2:E21” in the place of the “criteria_range1” argument
- Press “,“
- For the “criteria1” argument, type “1/1/2023” which represents the 1st of January 2023
(As this is a date value, make sure to use double quotation marks; otherwise, the formula won’t work at the end) - Press “,“
- Now, replace the “criteria_range2” argument with the “C2:C21”
- Press “,“
- For the second criterion, let us use the comparison operator and type”>5“
(Make sure to use the double quotation marks) - Complete the parentheses using “)“
- Press “Enter” key
Our formula is as follows,
=SUMIFS(E2:E21,B2:B21,"1/1/2023",C2:C21,">5")The total sales generated by the products with more than 5 quantities sold in January is $5,581.
It was a simple example. So, make sure to use the comparison operators such as less than (<) and equal to (>) in case of complex examples.
Example #4 – Using the SUMIFS in Google Sheets with the Blank Cells as criteria
In the earlier examples, we discussed finding the sum of the cells that hold a specific text string or number. But what if you have to find the sum of the cells corresponding to the blank cells in another column?
It is pretty straightforward. Thanks to comparison operators!
Consider the following example,
We have e-commerce product sales data. It includes the product price and sales data.
It seems like Red and Green Apples are in bad luck! They have 0 sales.
Let us find the cost of the Red and Green Apples inventory from the above table. Here, our criteria are simple.
- The product name should include the keyword “Apple”
- Product quantities sold over time should be zero
Note that here, we need to use the Metacharacter named the Asterisk (*) to find the cells that contain the keyword “Apple”
Here are the steps,
- Select the desired cell
- Type “=sumifs”
- Choose the first option from the popup or press the “Tab” key
- Replace the “sum_range” argument with the cell range “E2:E10“
- Press “,” to move to the next argument of the SUMIFS function
- Now, let us put “A2:A10” in the place of the “criteria_range1” argument
- Press “,“
- For the first criterion, let us use the wildcard operator and type “*Apple“
(Make sure to put both the asterisk (*) symbol and Apple keyword in the double quotation marks) - Press “,“
- Next, replace the “criteria_range2” argument with the cell range “C2:C10“
- Press “,“
- The second criterion includes finding the blank cells. So, we will use the comparison operator and type “=“
(Note that it is an equal (=) symbol put within the double quotation marks) - Complete the parentheses using “)“
- Press the “Enter” key
The formula to sum the blank cells is as follows,
=SUMIFS(E2:E10,A2:A10,"*Apple",C2:C10,"=")So, the total inventory cost of the apples that haven’t been sold yet is $5,518.
Similarly, to find the sum of the cells that correspond to nonblank cells, you need to use “<>”. In short, all you need to do is replace “=” with “<>”. Refer to the following formula.
=SUMIFS(E2:E10,A2:A10,"*Apple",C2:C10,"<>")How to use the SUMIFS function with Multiple Conditions in One Column (OR Logic)
The examples discussed above let users define a single criteria in one column.
For example, consider the table below.
If you wish to use the following formula to find the total annual salary of the employees from Phoenix and Austin who are working in the IT department,
=SUMIFS(D2:D20,B2:B20,"Phoenix",”Austin”,C2:C20,"IT")It will return the following error.
In this case, you need to combine two SUMIFS formulas with different criteria as follows,
=SUMIFS(D2:D20,B2:B20,"Phoenix",C2:C20,"IT")+SUMIFS(D2:D20,B2:B20,"Austin",C2:C20,"IT")Explanation of the Above Formula
The formula can be divided into two parts joined using the “+” symbol.
The first one lets us find the annual salary of the IT department employees from Phoenix.
SUMIFS(D2:D20,B2:B20,"Phoenix",C2:C20,"IT")For the next one, let’s find the annual salary of the IT department employees from Austin.
SUMIFS(D2:D20,B2:B20,"Austin",C2:C20,"IT")Note that it is the only way in Google Sheets to create and test multiple criteria within one column.
FAQs
Q. What is the difference between the SUMIF and SUMIFS functions in Google Sheets?
The SUMIF function can be called the singular form of the SUMIFS function. It is used to find the sum of cells that satisfies the given criteria. At the same time, the SUMIFS is helpful in cases where we need to find the sum of the cells that meet multiple criteria defined by the users.
Here’s the syntax for the SUMIF function,
=SUMIF(range, criteria, [sum_range])As you may have guessed, the syntax of the SUMIF function allows you to define only one criterion or condition.
On the other hand, the syntax for the SUMIFS function is as follows,
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])It lets users define any number of criteria and find the sum of the corresponding cells that satisfies all of the user’s conditions.
To know more about the SUMIF function, read our article: How to Use the SUMIF Function in Google Sheets [With Examples]
Q. How many criteria can users define within the SUMIFS function?
Users can define up to 127 criteria using the SUMIFS functions. This number is quite huge and enough for most of the complex datasets.
Q. How do you access the SUMIFS function from the Google Sheets main menu?
You have two ways to access a formula or function in Google Sheets.
The first includes selecting the cell, putting the equal (=) sign, and typing the formula.
For the second method, you can follow the steps below:
- Open the Google Sheet
- Hover to the main menu
- Click on the “Insert” tab
- Select the “Function” option from the popup
- Next, click on the “Math” from the list of available functions
- A new popup will appear. Scroll down and choose “SUMIFS“
Both ways are pretty straightforward.
However, selecting the cell and typing the formula sounds quick.
Q. Can I add the two SUMIFS formulas together?
Yes. It is possible to add two separate SUMIFS formulas with different criteria.
We have discussed the example in the previous section of this article. The final formula we derived looks like this,
=SUMIFS(D2:D20,B2:B20,"Phoenix",C2:C20,"IT")+SUMIFS(D2:D20,B2:B20,"Austin",C2:C20,"IT")It contains two SUMIFS with different criteria in one column.
Conclusion
If you wish to master both the SUMIF and SUMIFS functions in Google Sheets, practice is necessary.
They are beneficial to e-commerce business owners.
So, make sure to download the example Google Sheet provided at the start of this article and practice the examples discussed in the above sections.
I hope this article taught you all the bells and whistles of using the SUMIFS function in Google Sheets.
Feel free to comment below if you are stuck somewhere or having any particular issue.
Also, explore our blog for more Google Sheets tips and tricks.