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

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

**Explanation of Arguments**

**sum_range**: The range of cells to sum.**criteria_range1**: The content of cells you want to apply the first criteria against.**criteria1**: The condition that must be met for the first criteria content.**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**

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Product | Region | Sales | Product | Result |

2 | Apples | East | 100 | Apples | |

3 | Oranges | West | 200 | East | |

4 | Apples | East | 150 | ||

5 | SUMIFS(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**

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Product | Month | Sales | Month | Result |

2 | Apples | Jan | 100 | Jan | |

3 | Apples | Feb | 200 | Apples | |

4 | Oranges | Jan | 150 | ||

5 | SUMIFS(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**

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Region | Salesperson | Sales | Region | Result |

2 | East | John | 100 | East | |

3 | West | Jane | 200 | John | |

4 | East | John | 150 | ||

5 | SUMIFS(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**

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Price | Quantity | Sales | Price | Result |

2 | 10 | 5 | 100 | 5 | |

3 | 15 | 3 | 200 | 10 | |

4 | 20 | 4 | 150 | ||

5 | SUMIFS(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**

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Category | Discount | Sales | Category | Result |

2 | Electronics | 10% | 100 | Electronics | |

3 | Furniture | 5% | 200 | 5% | |

4 | Electronics | 10% | 150 | 10% | |

5 | SUMIFS(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**

A | B | C | D | E | F | |
---|---|---|---|---|---|---|

1 | Region | Salesperson | Sales | Region | Discount | Result |

2 | East | John | 100 | East | 10% | |

3 | West | Jane | 200 | John | ||

4 | East | John | 150 | |||

5 | IF(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**

A | B | C | D | E | F | |
---|---|---|---|---|---|---|

1 | Price | Quantity | Sales | Price | Tax | Result |

2 | 10 | 5 | 100 | 5 | 5% | |

3 | 15 | 3 | 200 | 10 | ||

4 | 20 | 4 | 150 | |||

5 | SUM(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**

A | B | C | D | E | F | |
---|---|---|---|---|---|---|

1 | Salesperson | Sales | Region | Name | Rate | Result |

2 | John | 1000 | East | John | 5% | |

3 | Jane | 2000 | West | East | ||

4 | John | 1500 | East | |||

5 | SUMIFS(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**

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Product | Region | Sales | Product | Result |

2 | Apples | East | 100 | Apples | |

3 | Oranges | West | 200 | East | |

4 | Apples | East | 150 | ||

5 | AVERAGE(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**

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Product | Region | Sales | Product | Result |

2 | Apples | East | 100.75 | Apples | |

3 | Oranges | West | 200.25 | East | |

4 | Apples | East | 150.50 | ||

5 | ROUND(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**

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Product | Month | Sales | Month | Result |

2 | Apples | Jan | 100 | Jan | |

3 | Apples | Feb | 200 | Apples | |

4 | Oranges | Jan | 150 | ||

5 | SQRT(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**

A | B | C | D | E | F | |
---|---|---|---|---|---|---|

1 | Product | Month | Sales | Price | Month | Result |

2 | Apples | Jan | 100 | 2 | Jan | |

3 | Apples | Feb | 200 | 3 | Apples | |

4 | Oranges | Jan | 150 | |||

5 | SUMIFS(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**

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Product | Month | Sales | Month | Result |

2 | Apples | Jan | 100 | Jan | |

3 | Apples | Feb | 200 | Apples | |

4 | Oranges | Jan | 150 | ||

5 | LOG(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**

**SUMIFS**is not case-sensitive.- You can use logical operators (>,<,<>,=) in criteria.
- Ensure the criteria ranges are the same size as the sum range.
- If you need to evaluate a single condition, you can use the
**SUMIF**function. - If you want to sum based on a date range, ensure the date is in a recognizable Excel date format.
- 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. **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.