Excel boasts 400+ functions to perform various mathematical and statistical calculations. You can easily find the sum of cells satisfying multiple conditions using the SUMIFS function. It is an easy-to-use function and an advanced version of the popular SUMIF function.
It only adds numbers from a given range when all conditions are satisfied.
The function allows you to define multiple criteria or conditions that need to be tested against a range, and then each cell that satisfies all of those conditions is added.
This is a quick guide about the SUMIFS function in Excel. Let us dive deep into the syntax and use cases of this function. Note that we are about to discuss various SUMIFS examples, so read the article until the end.
Download the Example Excel Sheets
Before we proceed further, make sure to download the following Excel spreadsheet, which includes the dataset used to demonstrate the SUMIFS function in this article.
Practice is the key to success, so follow me along as we move through each SUMIFS example discussed in the upcoming sections. It is okay if you have your own Excel spreadsheet ready with the required dataset; skip downloading the above file.
Why use the Excel 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.
Here, the standard SUMIF function is not helpful. We need to use the SUMIFS function that allows us to define multiple criterias.
Using the SUMIFS function, users can define multiple criteria or conditions to find the sum of the cells that contain,
- The name of the given product
- The months between January and April
The function adds up the corresponding cell values only if all of these conditions are met to find the product sales between January and April.
Explained: The SUMIFS Function in Excel
The SUMIFS is a conditional function in Excel. It lets users find the sum of the cells that satisfy the given criteria.
Anyone can use this function without any prior experience with spreadsheet applications, thanks to the simple syntax.
Here’s how the general syntax for the function looks like,
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, …])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” – This argument represents the cell range that will be tested against the criteria defined in the formula. 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 argument are added to find the sum.
- “criteria1” – Here, users can define their first criteria. The cirteria_range1 argument needs to satisfy this criterion. It includes logical expressions with numbers, letters, and dates.
- “criteria_range2” – This is an optional argument. It represents another cell range, which will be tested against the second criterion defined by the users.
- “criteria2” – 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.
For the criteria arguments, you can manually enter the values or provide the cell references holding the values. There is no limit to defining the number of criteria; you can define as many criteria as you can.
It is compulsory to define at least one criterion for this function to work.
Note that the Metacharacters and Comparison Operators are also used to define the criteria. Here is the list of Comparison Operators that can be used within the SUMIFS function:
- “=” – 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 with the help of examples in the upcoming sections of this article.
3 Things to know before using the SUMIFS function in Excel
You may quickly learn to use the SUMIFS function. However, there are a few things to consider to avoid misleading calculations and errors.
Here, I have mentioned the most common mistakes made by Excel users while using the SUMIFS function.
1. Equal range should be defined within the arguments “sum_range” and “criteria_range”
You need to make sure that 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 an error. It is because the sum_range is “C2:C21” and criteria_range is “A2:A31”. Ideally, the criteria_range should be “A2:A21”.
After correcting the criteria_range1 argument, the function works correctly. Refer to 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 requirements are satisfied.
Consider the following example,
Here, we 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 will find the sum of monthly sales of Green Apples when it exceeds 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 specified within the formula must be enclosed in 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 Excel (AND Logic)
You don’t need expert knowledge to use the SUMIFS function in Excel. You only need to have a complete understanding of the logic or criteria that need to be used.
Let us discuss different examples and learn to combine the SUMIFS with other functions using the AND logic.
Here are a few of the basics:
- SUMIFS with Text Criteria
- SUMIFS with Number Criteria
- SUMIFS with Date Criteria
Example #1 – Using the SUMIFS in Excel with the Text Strings as criteria
This is the most common scenario. It is the simplest one, where the logical expressions include the text strings.
Consider the following example with employee details such as their name, city, department, and annual salary.
Our task is to find the salary of the IT employees from Phoenix city.
Note that we have two criteria here: Phoenix City and 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” argument with the cell range “B2:B20“
- Press “,“
- Now, our first criterion is the employees from Phoenix City. So, we will replace the “criteria1” argument with the keyword “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 enclose the condition in double quotation marks) - Complete the bracket using “)” on your keyboard
- Press the “Enter” key
Our final formula is as follows,
=SUMIFS(D2:D20,B2:B20,"Phoenix",C2:C20,"IT")Excel will instantly calculate the annual salary of the employees from Phoenix and working in the 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 Excel 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 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.
Consider the following dataset where we have employee details including full name, total sales generated, working years, and annual bonus.
Here, we will be finding the total sales generated by the employees who have recently joined in the last 2 years and received a bonus greater than $10,000.
Let’s begin,
- 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 following 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 enclose the criteria in double quotation marks) - Complete the bracket using “)“
- Press “Enter” on your keyboard
The final 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 Excel with the Dates as criteria
Using dates as a criterion is quite challenging and requires an in-depth understanding of the comparison operators. We will discuss a simple scenario as follows,
The above table includes product sales data with their price and quantity sold between January to March.
Let us find the total sales in January by the products whose 5 or more quantities have been sold.
- 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 the “Enter” key
Our final 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. You need to master the comparison operators such as less than (<) and equal to (>) in the case of complex examples.
Example #4 – Using the SUMIFS in Excel with the Blank Cells as criteria
In the previous examples, we have discussed how to find the sum of the cells that hold a specific text string or number.
But what if you need to find the sum of the cells corresponding to the blank cells in another column?
Consider the following example,
We have a dataset that includes product price and sales data.
Our task is to find the cost of the Red and Green Apples inventory. 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”.
Let’s begin,
- 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 error as “You’ve entered a few arguments for this function”.
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
This formula can be divided into two parts and is joined using the “+” symbol.
The first SUMIFS lets us find the annual salary of the IT department employees from Phoenix city.
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 the city of Austin.
SUMIFS(D2:D20,B2:B20,"Austin",C2:C20,"IT")Note that it is the only way in Excel to create and test multiple criteria within one column.
FAQs
Q. What is the difference between the SUMIF and SUMIFS functions in Excel?
The SUMIF and SUMIFS functions in Excel are both used to sum values based on specified criteria, but they differ in how many criteria they can handle.
SUMIF is used to find the sum of cells that satisfies single criteria defined by the users.
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 can see, there is only one “criteria” argument in the above function.
Similarly, 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 satisfy all user-defined conditions.
Q. How do you access the SUMIFS function from the Excel main menu?
There are two ways to access the SUMIFS function in Excel.
The first one includes selecting the cell, putting the equal (=) sign, and typing the formula right away.
Whereas for the second method, you need to follow the steps below:
- Open the Excel Sheet
- Hover to the main menu
- Click on the “Formulas” tab
- Select the “Insert Function”
- A new dialog box will appear, as shown below
- In the Search field, type in the formula you wish to insert and click on the “Go” button, as shown in the following image
(Here, we will enter “SUMIFS”) - Select the function as shown below
- Click the “OK” button
- After this, you will be prompted to a “Function Arguments” dialog box where you will enter the arguments and click the “OK” button once done
Q. Can I add two SUMIFS formulas together?
Yes. You can.
It is possible to add two separate SUMIFS formulas with different criteria.
We have discussed the example in the last section of this article. The final formula we derived is as follows,
=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.
To Summarize: The SUMIFS Excel
If you wish to master both the SUMIF and SUMIFS functions in Excel, practice is critical. Make sure to download the example Excel sheet attached at the beginning and practice the examples discussed throughout the article.
The function is easy to use, but you need to have a clear understanding of the criteria.
I hope this article taught you all the bells and whistles of using the SUMIFS function in Excel. If you wish to learn the SUMIF function, refer to our tutorial using the following link.
How to Use the SUMIF Function in Excel [With Examples]
Feel free to comment below if you are stuck somewhere or having any particular issue while using the SUMIFS function in Excel
Also, explore our blog for more tips and tricks to use Microsoft Excel and Google Sheets like a pro.