# SUMIFS Function in Excel

Part 1: Introduce

Definition

The SUMIFS function in Microsoft Excel is a powerful tool that sums values based on multiple criteria.

Purpose

The purpose of the SUMIFS function is to provide a sum of certain cells that meet multiple specific conditions. It’s an extension of the SUMIF function, allowing for more complex scenarios where several needs must be met.

Syntax & Arguments

The syntax for the SUMIFS function is as follows:

syntax
`SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) `

Explanation of Arguments

1. sum_range: The range of cells to sum.
2. criteria_range1: The content of cells you want to apply the first criteria against.
3. criteria1: The condition that must be met for the first criteria content.
4. criteria_range2, criteria2, …: You can specify additional fields and criteria (up to 127 pairs).

Return Value

The SUMIFS function returns a numerical value, representing the sum of the cells that meet all the specified criteria.

Remarks

• The SUMIFS function is not case-sensitive.
• All criteria must be met to include a value in the sum.

Part 2: Examples

Example 1

Purpose of Example: Sum sales of a specific product in a region.

Data Tables and Formulas

ABCDE
1ProductRegionSalesProductResult
2ApplesEast100Apples
3OrangesWest200East
4ApplesEast150
5SUMIFS(C2:C4, A2:A4, D2, B2:B4, D3)

Result in Cell E5: 250

Explanation: This example sums up the sales for “Apples” in the “East” region. The SUMIFS function checks the product and region criteria and sums the corresponding sales values.

Example 2

Purpose of Example: Sum sales for a specific product during a month.

Data Tables and Formulas

ABCDE
1ProductMonthSalesMonthResult
2ApplesJan100Jan
3ApplesFeb200Apples
4OrangesJan150
5SUMIFS(C2:C4, A2:A4, D3, B2:B4, D2)

Result in Cell E5: 100

Explanation: This example sums up the sales for “Apples” in the month of “Jan”. The SUMIFS function checks the product and month criteria and sums the corresponding sales values.

Example 3

Purpose of Example: Sum sales for a specific region and salesperson.

Data Tables and Formulas

ABCDE
1RegionSalespersonSalesRegionResult
2EastJohn100East
3WestJane200John
4EastJohn150
5SUMIFS(C2:C4, A2:A4, D2, B2:B4, D3)

Result in Cell E5: 250

Explanation: This example sums up the sales for the “East” region and salesperson “John”. The SUMIFS function checks the region and salesperson criteria and sums the corresponding sales values.

Example 4

Purpose of Example: Sum sales for products above a specific price and below a certain quantity.

Data Tables and Formulas

ABCDE
1PriceQuantitySalesPriceResult
21051005
315320010
4204150
5SUMIFS(C2:C4, A2:A4, “>”&D2, B2:B4, “<“&D3)

Result in Cell E5: 350

Explanation: This example sums the sales for products above a price of 5 and below a quantity of 10. The SUMIFS function checks the price and quantity criteria and sums the corresponding sales values.

Example 5

Purpose of Example: Sum sales for a specific category and discount range.

Data Tables and Formulas

ABCDE
1CategoryDiscountSalesCategoryResult
2Electronics10%100Electronics
3Furniture5%2005%
4Electronics10%15010%
5SUMIFS(C2:C4, A2:A4, D2, B2:B4, “>=”&D3)

Result in Cell E5: 250

Explanation: This example sums the sales for the “Electronics” category with a discount range of 5% to 10%. The SUMIFS function checks the category and discount criteria and sums the corresponding sales values.

Example 6

Purpose of Example: Use the IF function to sum sales for a specific region and salesperson and apply a discount if the total exceeds a particular value.

Data Tables and Formulas

ABCDEF
1RegionSalespersonSalesRegionDiscountResult
2EastJohn100East10%
3WestJane200John
4EastJohn150
5IF(SUMIFS(C2:C4, A2:A4, D2, B2:B4, D3)>200, SUMIFS(C2:C4, A2:A4, D2, B2:B4, D3)*E2, SUMIFS(C2:C4, A2:A4, D2, B2:B4, D3))

Result in Cell F5: 225

Explanation: This example sums the sales for the “East” region and salesperson “John” and applies a 10% discount if the total sales exceed 200. The SUMIFS function checks the region and salesperson criteria and sums the corresponding sales values. The IF function then applies the discount if the condition is met.

Example 7

Purpose of Example: Sum sales for products above a specific price and below a certain quantity, and then calculate the total, including tax, using the SUM function.

Data Tables and Formulas

ABCDEF
1PriceQuantitySalesPriceTaxResult
210510055%
315320010
4204150
5SUM(SUMIFS(C2:C4, A2:A4, “>”&D2, B2:B4, “<“&D3), SUMIFS(C2:C4, A2:A4, “>”&D2, B2:B4, “<“&D3)*E2)

Result in Cell F5: 367.5

Explanation: This example calculates the total sales for products above a price of 5 and below a quantity of 10 and then adds a 5% tax. The SUMIFS function checks the price and quantity criteria and sums the corresponding sales values. The SUM function then 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 specific criteria.

Data Tables and Formulas

ABCDEF
1SalespersonSalesRegionNameRateResult
2John1000EastJohn5%
3Jane2000WestEast
4John1500East
5SUMIFS(B2:B4, A2:A4, D2, C2:C4, D3) * VLOOKUP(D2, A2:E3, 5, FALSE)

Result in Cell F5: 125

Explanation: This example calculates the total commission for “John” in the “East” region based on his sales. The SUMIFS function sums the deals for “John” in the “East” region, 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 and region using the AVERAGE function.

Data Tables and Formulas

ABCDE
1ProductRegionSalesProductResult
2ApplesEast100Apples
3OrangesWest200East
4ApplesEast150
5AVERAGE(SUMIFS(C2:C4, A2:A4, D2, B2:B4, D3))

Result in Cell E5: 125

Explanation: This example calculates the average sales for “Apples” in the “East” region. The SUMIFS function checks the product and region criteria and sums the corresponding sales values. The AVERAGE function then calculates the average.

Example 10

Purpose of Example: Calculate the total sales for a specific product and region and then round the result using the ROUND function.

Data Tables and Formulas

ABCDE
1ProductRegionSalesProductResult
2ApplesEast100.75Apples
3OrangesWest200.25East
4ApplesEast150.50
5ROUND(SUMIFS(C2:C4, A2:A4, D2, B2:B4, D3), 0)

Result in Cell E5: 251

Explanation: This example calculates the total sales for “Apples” in the “East” region and then rounds the result to the nearest whole number. The SUMIFS function sums the sales, and the ROUND function rounds the result.

Example 11

Purpose of Example: Calculate the total sales for a specific product in a particular month and then find the square root using the SQRT function.

Data Tables and Formulas

ABCDE
1ProductMonthSalesMonthResult
2ApplesJan100Jan
3ApplesFeb200Apples
4OrangesJan150
5SQRT(SUMIFS(C2:C4, A2:A4, D3, B2:B4, D2))

Result in Cell E5: 10.05

Explanation: This example calculates the total sales for “Apples” in the month of “Jan” and then finds the square root of the total. The SUMIFS 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 in a particular month and then multiply by a price found using the INDEX and MATCH functions.

Data Tables and Formulas

ABCDEF
1ProductMonthSalesPriceMonthResult
2ApplesJan1002Jan
3ApplesFeb2003Apples
4OrangesJan150
5SUMIFS(C2:C4, A2:A4, E3, B2:B4, E2) * INDEX(D2:D3, MATCH(E3, A2:A3, 0))

Result in Cell F5: 200

Explanation: This example calculates the total sales for “Apples” in the month of “Jan” and then multiplies by the price found using the INDEX and MATCH functions. The SUMIFS 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 in a particular month and then find the logarithm using the LOG function.

Data Tables and Formulas

ABCDE
1ProductMonthSalesMonthResult
2ApplesJan100Jan
3ApplesFeb200Apples
4OrangesJan150
5LOG(SUMIFS(C2:C4, A2:A4, D3, B2:B4, D2))

Result in Cell E5: 4.61

Explanation: This example calculates the total sales for “Apples” in the month of “Jan” and then finds the natural logarithm of the total. The SUMIFS function sums the sales, and the LOG function calculates the logarithm.

Part 3: Tips and Tricks

1. SUMIFS is not case-sensitive.
2. You can use logical operators (>,<,<>,=) in criteria.
3. Ensure the criteria ranges are the same size as the sum range.
4. If you need to evaluate a single condition, you can use the SUMIF function.
5. If you want to sum based on a date range, ensure the date is in a recognizable Excel date format.
6. In the criteria argument, you can use wildcard characters like “?” and ““. “?” matches any single character, while “” matches any sequence of characters. If you want to find these characters exactly, use a tilde (~) in front of the character.
7. SUMIFS adds values based on multiple criteria, so all conditions must be met to include a value in the sum. Theount will not be included in the sum.