In excel we have two functions on conditions and they are sumif and sumifs, sumifs has the logic to work with multiple conditions while sumif uses the logic for one condition but there are another ways when we can use sumif function with multiple criteria and this is done by using the logical functions AND and OR.
SUMIF with Multiple Criteria (Table of Contents)
SUMIF with Multiple Criteria in Excel
As the name suggests itself, SUMIF (SUM + IF) function sums the values of the cells based on the conditions provided. Criteria can be based on:
We can use various type of operators while specifying the criteria:
- > (Greater than)
- < (Less than)
- >= (Greater than or Equal to)
- <= (Less than or Equal to)
- = (Equal to)
- <> (Not equal to)
- * (A wildcard character: It means zero or more characters)
- ? (A wildcard character: It means any one character)
The syntax of the SUMIF function is
There are 3 arguments in SUMIF function.
- Range: Here the range is the range of cells that we want to be evaluated by the criteria.
- Criteria: We need to specify the criteria in the form of a number, a cell reference, an expression, text, or a function (that defines which values will be added). Examples are:
- 64 (Number)
- “>64” (Expression)
- C6 (A cell reference)
- “Delhi” (Text)
- TODAY()-3 (A function that indicates that day should be 3 days earlier to today)
- Sum_range: This is an optional argument. If we omit this argument, it indicates that we want to sum up the same range that we have specified as the first argument. In case, sum range and criteria range, both are different then we need to mention sum_range. Size of range and sum_range should be the same.
Examples of SUMIF with Multiple Criteria
Let us understand SUMIF with Multiple Criteria in excel with the help of an example.
SUMIF with Multiple Criteria – Example #1
Suppose, we have the following data for sales of an organization. We want to sum sales based on various criteria.
Now if you want to sum the sales of laptop only, then the formula would look like:
The range is the ‘Item’ field which we want to compare with criteria (item should be “Laptop”) and “Total Sales Amount” is the sum_range.
After pressing the Enter button, the result would look like:
However, this formula is not that much usable as the user who does not know much about the MS Excel, will not be able to use this sheet. As you can see, we have specified “Laptop” literally for the criteria argument. We need to give the reference of a cell where we need to type the value or we can create drop down.
To do the same, the steps are:
- Select cell F5 where we had written “Laptop”. Delete the written word. Go to Data Tab -> Data Validation Command under Data Tools
- Choose Data Validation from the list.
- Choose “List” for “Allow” Type “Laptop, Tablet, Mobile” for “Source” window as these are our unique products. Click on OK.
The dropdown has been created.
- We need to make a small change in the formula that is to give the reference of cell F5 for the “criteria”
Now whenever we change the value of cell F5 by selecting from the drop-down, the “Total Sales Amount” gets changed automatically.
SUMIF with Multiple Criteria – Example #2
Suppose, we have the following list of products of a brand and its total sales value.
We want to add the value where the item name contains “top”.
To do the same, the formula would be
And the answer will be,
This formula has added the value for “Laptop”, “Desktop” and “Laptop Adapter”.
SUMIF with Multiple Criteria – Example #3
Suppose, we have the following list of products of a brand and their total sales value.
We want to add the value where the item name contains “*” as we have used * to indicate special items where there is more margin.
In Excel, “*” is a wildcard character, but if we need to find this character then we need to use escape character which is tilde (~) to escape the real meaning of this character.
The formula would look like:
As you can see, we have used “*~**” as criteria. First and the last asterisk has been written to indicate that asterisk can be anywhere in the product name. It can be the first character, last character or any character in between.
Between the asterisk, we have used tilde (~) sign with an asterisk (*) for product names containing asterisk sign.
The result would look as follows:
SUMIFS Function with Multiple Criteria
SUMIFS and SUMIF, both, to very much extent, are the same. The only difference between both is that SUMIF allows us to specify only one condition whereas SUMIFS allows us to specify more than one criteria (maximum 127 criteria).
SUMIFS with Multiple Criteria Example
Suppose, we have the same sales data as in Example 1. We will use SUMIFS for specifying more than one criteria.
We want that as we enter the item name, location and company name, we get the sum of the total sales amount. To do the same, first, we need to understand the syntax of the SUMIFS formula.
The syntax of the SUMIFS is
As we can see, arguments are:
- Sum_range: This is the range of cells, which we want to sum up if all the conditions are met.
- Criteria_range1: This is the range of cells to compare with criteria1.
- Criteria1: For this argument, we need to specify the criteria using text, cell reference, and expression with a various operator as discussed above which would be compared with criteria_range1.
- Criteria_range2: This is the range of cells to compare with criteria1
- Criteria2: For this argument, we need to specify the criteria using text, cell reference, and expression with a various operator as discussed above which would be compared with criteria_range2.
And so on up to 127 criterions.
Let us define the SUMIFS function for our problem.
The function would be specified as bellows:
We can create dropdowns using data validation as we have done in Example 1. And then select the location, Item and Company Name as Banglore, Laptop and Samsung respectively.
So the result is as follows,
Things to Remember
While specifying sum_range and criteria range for the SUMIF function, we need to make sure that the size of both ranges are equal as corresponding value is summed up of sum_range if the condition is satisfied in criteria_range. It applies to SUMIFS function as well. All the criteria ranges and sum_range must be of the same size.
You can download this Sumif with Multiple Criteria Excel Template from here – SUMIF with Multiple Criteria Excel Template
This has been a guide to SUMIF with Multiple Criteria in Excel. Here we discuss Examples of SUMIF Function with Multiple Criteria in Excel along with downloadable excel template. You may also look at these useful functions in excel –