# SUMIF Function in Excel

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:

syntax
```SUMIF(range, criteria, [sum_range]) ```

Explanation of Arguments

1. Range: This is the range of cells you want to apply the criteria against.
2. 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.
3. 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

ABC
1ProductSales
2Apples100
3Oranges200
4Apples150
5SUMIF(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

ABC
1ProductSales
2Apples100
3Oranges200
4Bananas150
5SUMIF(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

ABCD
1ProductSalesCriterion
2Apples100Apples
3Oranges200
4Bananas150
5SUMIF(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

ABC
1ProductSales
2Apples100
3Oranges200
4Bananas150
5SUMIF(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

ABC
1ProductSales
2Red Apples100
3Green Apples200
4Oranges150
5SUMIF(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

ABCD
1ProductSalesDiscountResult
2Apples10010%
3Oranges200
4Apples150
5IF(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

ABCD
1ProductSalesTaxResult
2Apples1005%
3Oranges200
4Apples150
5SUM(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

ABCDE
1SalespersonSalesCommissionNameResult
2John10005%John
3Jane20003%
4John1500
5SUMIF(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

ABC
1ProductSalesResult
2Apples100
3Oranges200
4Apples150
5AVERAGE(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

ABC
1ProductSalesResult
2Apples100.75
3Oranges200.25
4Apples150.50
5ROUND(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

ABC
1ProductSalesResult
2Apples100
3Oranges200
4Apples150
5SQRT(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

ABCDE
1ProductSalesPriceNameResult
2Apples1002Apples
3Oranges2003
4Apples150
5SUMIF(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

ABC
1ProductSalesResult
2Apples100
3Oranges200
4Apples150
5LOG(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

1. SUMIF is not case-sensitive.
2. In the criteria argument, you can use wildcard characters like “?” and “. “?” to match any single personality, while “” matches any sequence of characters.
3. If you use wildcard characters as literal characters.
4. You can use logical operators (>,<,<>,=) in criteria.
5. If sum_range is omitted, the cells in the range are evaluated by criteria and added if they match the requirements.
6. SUMIF function only supports one condition. If you need to evaluate multiple conditions, use the SUMIFS function.