How to use sumifs formula in Excel?
SUMIFS is a mathematic function used in Excel to get the sum of cells with multiple criteria. It adds up the cells data that meet the specified criteria. It is an alternative function to SUMIF but with additional functionality. Basically, SUMIFS is an advanced version of the SUMIF function.
Unlike the SUMIF function, SUMIFS allows the users to apply multiple criteria at once and get the sum of specific cell data that satisfies the specified criteria.
In this chapter, we will give you a complete guide of the SUMIFS function with examples. Each step will be explained so you will not face any problems.
Formula Syntax
Following is the syntax for SUMIFS function:
This function allows to enter up to 127 range pairs for this formula.
Where parameters are –
Sum Range (Required) – Provide the range of cells to sum, e.g., C2:C15.
Criteria_range1 (Required) – Provide the range of cells on which you want to apply the criteria. It is simply the cell range, e.g., B3:B9.
Criteria1 (Required) – Provide the criteria against the specified criteria_range1 to sum. The value must be passed in double quotation marks of this parameter, i.e., “=North” or “>=50”.
Criteria_rangeN, criteriaN (Optional) – N number of additional range of cells along with their criteria. You can pass up to 127 range of pairs, so it is optional.
Return Value
SUMIFS function returns a numeric value after summed up the cell’s data.
When is SUMIFS used?
As we already told you, the SUMIFS function helps to sum the cell data along with multiple conditions/criteria. Let’s take the examples and usually see when this function is used.
For example,
Condition 1: If you want to know the total number of orders placed by those who reside in Delhi and in North Zone, too.
Condition 2: If you want to find the total of orders placed by those who reside in Delhi and the order quantity is more than equal to 15.
This type of situation requires a formula where we can put multiple criteria in one formula and get the result. Now, we will show how it will be done practically over an Excel worksheet.
Example 1
We have an Excel worksheet containing some data in it. This Excel sheet is having the Name, City, Zone, and the number of orders details.
In this example, we will find out the total number of orders placed by those who reside in Delhi and in North Zone too.
Follow the steps below:
Step 1: Create an Excel sheet, as shown above, to learn the working of SUMIFS formula.
Step 2: Select a cell where you like to paste the calculated result.
Step 3: Go to the Formula Bar and write the SUMIFS formula in it.
Step 4: Copy and paste the following SUMIF formula in the Formula bar to get the total number of orders placed by those who live in Delhi and in the North Zone.
Step 5: Press the Enter key to get the sum of cells that satisfy the specified conditions and see the calculated result showing in the Excel sheet below.
Step 6: You can also calculate manually and verify that the total number of orders is 48 by those who live in “Delhi North Zone“.
Now, you have verified that the SUMIFS function is working correctly and you have chosen all correct parameters to get the result accordingly.
Use of comparison operator in SUMIFS function
The SUMIFS function can also allow using comparison operators (like <, >, <=, >=) in it. We can use these operators to summed up the cells while putting a criteria range on Excel data. Basic Comparison operators are –
< (Less than operator)
< = (Less than Equal to)
> (Greater than operator)
>= (Greater than equal to)
Example
Now, we will take one more example to calculate the sum of cells having multiple criteria. We will show you the use of comparison operators in SUMIFS function.
We will use the same Excel worksheet as used in the above example. This Excel sheet consists of the Name, City, Zone, and the number of orders details.
In this example, we will find out the total orders placed by those who reside in Delhi and place more than 15 orders.
Follow the steps below:
Step 1: Create an Excel sheet, as shown above, to learn the working of the SUMIFS formula.
Step 2: Select a cell where you like to paste the calculated result.
Step 3: Go to the Formula Bar and write the SUMIFS formula in it.
Step 4: Copy and paste the following SUMIF formula in the Formula bar to get the total orders placed by who resides in Delhi and order quantity is greater than equal to 15.
Step 5: Press the Enter key to get the sum of cells who satisfy the specified conditions and see the calculated result showing in Excel sheet below.
Step 6: You can also calculate manually and verify the result for the total number of orders is 88 by those who resides in “Delhi” and placed more than 15 orders.
Now, you have verified the result. You can see that both results are the same, which means that the SUMIFS function is working correctly and you have chosen all the correct parameters.
Use of wildcard operator in SUMIFS function
The wildcard operators are such operators (like * and ?) which are used to achieve some specific purpose in the SUMIFS function. These work differently than comparison operator, but the purpose is almost the same.
The use of these wildcard operators within the criteria argument in SUMIFS function allows the users to find the matches that are similar but not accurate. Basic wildcard operators are –
* (asterisk) –
This operator is called asterisk operator that is used to find any sequence of characters. Basically, it is used for partial search. It can be used before, after, or in-between the criteria for the partial search of data. For example,
abc* – It indicates to a string starts with abc but followed by any text.
*abc – It indicates a string that ends with abc.
*abc* – It indicates that the text string must contain certain text (abc) in a string.
* (Question Mark) –
It is another wildcard operator called question mark operator. Unlike the asterisk (*) operator, it refers to any single character. For example, if I used this character between a string like M?r, this can match March, Mars, Mark, or anything else.
For example, =SUMIFS(D2:D9,B2:B9,”=D*”, D2:D9,”Mar?”) It will refer to that the all instances will start with Mar and end with one last letter that can be varied.
What if given data contains an asterisk or an actual question mark?
Sometimes, the data already contains the asterisk or the question mark symbol. Then a question arises – how wildcard will use. In this type of scenario, we will suggest you to use a tilde (~) operator in front of the question mark. Just type ~ before the question mark there.
Named range with SUMIFS function
You can also provide named ranges within the SUMIFS function. Basically, it is a descriptive name of collections of cells in an Excel worksheet.
Common Issues
What happens when wrong parameters are passed, or values are incorrect.
| Problem | Description |
|---|---|
| The resultant value is 0 instead of the expected result data. | Ensure that criteria1,2, N parameter value is must be passed inside double quotation marks. |
| The result is incorrect when Sum_range have either TRUE or FALSE value. | When the Sum_range parameters contain either True or False value, they evaluated differently. |
SUMIF vs SUMIFS
Both SUMIF and SUMIFS are mathematical function used in Excel, to sum up the cells data by specifying some condition on cells data. But there is a big difference between them, which make them different from each other.
- SUMIF allows to only sum the data based on the associated criteria within the same data, while the SUMIFS function allows applying multiple criteria in Excel. It means that the users can apply multiple criteria at once to get the sum of specific cells data.
- Using the SUMIF function, the user can evaluate only one condition, whereas SUMIFS allows using multiple criteria at once.
- SUMIFS can be called as an advanced version of SUMIF, which is available from Excel 2007.
