**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.