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.