Part 1: Introduction
Definition
The SUMIF function in Microsoft Excel is a versatile tool that allows you to sum the values in a range that meets your specified criteria.
Purpose
The purpose of the SUMIF function is to provide a sum of certain cells based on a single, specific criterion. This is particularly useful in business scenarios where you must calculate totals for a particular category or condition.
Syntax & Arguments
The syntax for the SUMIF function is as follows:
SUMIF(range, criteria, [sum_range]) Explanation of Arguments
- Range: This is the range of cells you want to apply the criteria against.
- Criteria: This is the condition that must be met. It can be a number, expression, cell reference, or text that defines which cells will be added.
- Sum_range (optional): These are the actual cells to sum. If you omit this argument, Excel sums the cells specified in the range argument.
Return Value
The SUMIF function returns a numerical value. It’s the sum of the cells that meet your specified criteria.
Remarks
The SUMIF function is not case-sensitive. So, for example, “RED” and “red” are considered the same.
Part 2: Examples
Example 1
Purpose of Example: Sum sales of a specific product.
Data Tables and Formulas
| A | B | C | |
|---|---|---|---|
| 1 | Product | Sales | |
| 2 | Apples | 100 | |
| 3 | Oranges | 200 | |
| 4 | Apples | 150 | |
| 5 | SUMIF(A2:A4, “Apples”, B2:B4) |
Result: 250
Explanation: This formula sums the sales for “Apples” only. The range A2:A4 is searched for ” Apples ” criteria, and the corresponding sales values in B2:B4 are summed.
Example 2
Purpose of Example: Sum sales are more significant than a specific value.
Data Tables and Formulas
| A | B | C | |
|---|---|---|---|
| 1 | Product | Sales | |
| 2 | Apples | 100 | |
| 3 | Oranges | 200 | |
| 4 | Bananas | 150 | |
| 5 | SUMIF(B2:B4, “>150”, B2:B4) |
Result: 350
Explanation: This formula sums the sales that are greater than 150. The range B2:B4 is searched for sales values greater than 150, which are summed.
Example 3
Purpose of Example: Sum sales based on a cell criterion.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Sales | Criterion | |
| 2 | Apples | 100 | Apples | |
| 3 | Oranges | 200 | ||
| 4 | Bananas | 150 | ||
| 5 | SUMIF(A2:A4, C2, B2:B4) |
Result: 100
Explanation: This formula sums the sales for the product specified in cell C2. The range A2:A4 is searched for the criteria in C2 and the corresponding sales values in B2:B4 are summed.
Example 4
Purpose of Example: Sum sales of a product not equal to a specific value.
Data Tables and Formulas
| A | B | C | |
|---|---|---|---|
| 1 | Product | Sales | |
| 2 | Apples | 100 | |
| 3 | Oranges | 200 | |
| 4 | Bananas | 150 | |
| 5 | SUMIF(A2:A4, “<>Apples”, B2:B4) |
Result: 350
Explanation: This formula sums the sales for products that are not “Apples”. The range A2:A4 is searched for products not equal to “Apples”, and the corresponding sales values in B2:B4 are summed.
Example 5
Purpose of Example: Sum sales of a product that contains a specific text.
Data Tables and Formulas
| A | B | C | |
|---|---|---|---|
| 1 | Product | Sales | |
| 2 | Red Apples | 100 | |
| 3 | Green Apples | 200 | |
| 4 | Oranges | 150 | |
| 5 | SUMIF(A2:A4, “*Apples”, B2:B4) |
Result: 300
Explanation: This formula sums the sales for products containing the ” Apples ” text. The range A2:A4 is searched for any text that ends with “Apples”, and the corresponding sales values in B2:B4 are summed.
Example 6
Purpose of Example: Use the IF function to sum sales of a specific product and apply a discount if the total is more significant than a particular value.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Sales | Discount | Result |
| 2 | Apples | 100 | 10% | |
| 3 | Oranges | 200 | ||
| 4 | Apples | 150 | ||
| 5 | IF(SUMIF(A2:A4, “Apples”, B2:B4)>200, SUMIF(A2:A4, “Apples”, B2:B4)*C2, SUMIF(A2:A4, “Apples”, B2:B4)) |
Result in Cell D5: 225
Explanation: This example sums the sales for “Apples” and applies a 10% discount if the total sales exceed 200. The SUMIF function calculates the total sales for “Apples”, and the IF function applies the value if the condition is met.
Example 7
Purpose of Example: Sum sales of a specific product and then calculate the total, including tax, using the SUM function.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Sales | Tax | Result |
| 2 | Apples | 100 | 5% | |
| 3 | Oranges | 200 | ||
| 4 | Apples | 150 | ||
| 5 | SUM(SUMIF(A2:A4, “Apples”, B2:B4), SUMIF(A2:A4, “Apples”, B2:B4)*C2) |
Result in Cell D5: 262.5
Explanation: This example calculates the total sales for “Apples” and then adds a 5% tax. The SUMIF function calculates the total sales, and the SUM function adds the tax to the total.
Example 8
Purpose of Example: Use VLOOKUP to find the commission rate for a salesperson and then calculate the total commission based on sales that meet a specific criterion.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Salesperson | Sales | Commission | Name | Result |
| 2 | John | 1000 | 5% | John | |
| 3 | Jane | 2000 | 3% | ||
| 4 | John | 1500 | |||
| 5 | SUMIF(A2:A4, D2, B2:B4) * VLOOKUP(D2, A2:C3, 3, FALSE) |
Result in Cell E5: 125
Explanation: This example calculates the total commission for “John” based on his sales. The SUMIF function sums the deals for “John”, and the VLOOKUP function finds the corresponding commission rate. The result is the total commission.
Example 9
Purpose of Example: Calculate the average sales for a specific product using the AVERAGE function.
Data Tables and Formulas
| A | B | C | |
|---|---|---|---|
| 1 | Product | Sales | Result |
| 2 | Apples | 100 | |
| 3 | Oranges | 200 | |
| 4 | Apples | 150 | |
| 5 | AVERAGE(SUMIF(A2:A4, “Apples”, B2:B4)) |
Result in Cell C5: 125
Explanation: This example calculates the average sales for “Apples”. The SUMIF function sums the sales for “Apples”, and the AVERAGE function calculates the average.
Example 10
Purpose of Example: Calculate the total sales for a specific product and then round the result using the ROUND function.
Data Tables and Formulas
| A | B | C | |
|---|---|---|---|
| 1 | Product | Sales | Result |
| 2 | Apples | 100.75 | |
| 3 | Oranges | 200.25 | |
| 4 | Apples | 150.50 | |
| 5 | ROUND(SUMIF(A2:A4, “Apples”, B2:B4), 0) |
Result in Cell C5: 251
Explanation: This example calculates the total sales for “Apples” and then rounds the result to the nearest whole number. The SUMIF function sums the sales, and the ROUND function rounds the result.
Example 11
Purpose of Example: Calculate the total sales for a specific product and then find the square root using the SQRT function.
Data Tables and Formulas
| A | B | C | |
|---|---|---|---|
| 1 | Product | Sales | Result |
| 2 | Apples | 100 | |
| 3 | Oranges | 200 | |
| 4 | Apples | 150 | |
| 5 | SQRT(SUMIF(A2:A4, “Apples”, B2:B4)) |
Result in Cell C5: 15.81
Explanation: This example calculates the total sales for “Apples” and then finds the square root of the total. The SUMIF function sums the sales, and the SQRT function calculates the square root.
Example 12
Purpose of Example: Calculate the total sales for a specific product and then multiply by a price found using the INDEX and MATCH functions.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Sales | Price | Name | Result |
| 2 | Apples | 100 | 2 | Apples | |
| 3 | Oranges | 200 | 3 | ||
| 4 | Apples | 150 | |||
| 5 | SUMIF(A2:A4, D2, B2:B4) * INDEX(C2:C3, MATCH(D2, A2:A3, 0)) |
Result in Cell E5: 500
Explanation: This example calculates the total sales for “Apples” and then multiplies by the price found using the INDEX and MATCH functions. The SUMIF function sums the sales, and the INDEX and MATCH functions find the corresponding price.
Example 13
Purpose of Example: Calculate the total sales for a specific product and then find the logarithm using the LOG function.
Data Tables and Formulas
| A | B | C | |
|---|---|---|---|
| 1 | Product | Sales | Result |
| 2 | Apples | 100 | |
| 3 | Oranges | 200 | |
| 4 | Apples | 150 | |
| 5 | LOG(SUMIF(A2:A4, “Apples”, B2:B4)) |
Result in Cell C5: 5.52
Explanation: This example calculates the total sales for “Apples” and then finds the natural logarithm of the total. The SUMIF function sums the sales, and the LOG function calculates the logarithm.
Part 3: Tips and Tricks
- SUMIF is not case-sensitive.
- In the criteria argument, you can use wildcard characters like “?” and “. “?” to match any single personality, while “” matches any sequence of characters.
- If you use wildcard characters as literal characters.
- You can use logical operators (>,<,<>,=) in criteria.
- If sum_range is omitted, the cells in the range are evaluated by criteria and added if they match the requirements.
- SUMIF function only supports one condition. If you need to evaluate multiple conditions, use the SUMIFS function.