Excel SUMIF with Multiple Criteria
“Sumif with multiple criteria” 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 dates, numbers, and text. 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.
Let us understand SUMIF with Multiple Criteria in excel with the help of an example.
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.
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”.
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:
Things to Remember
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 anyone character)
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.
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 –