The **SUMIFS** function in Excel is a powerful tool used for adding up values in a range based on multiple criteria. It extends the **SUMIF** function to allow for multiple criteria.

Table of Contents

**SUMIFS **Syntax

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

**sum_range**: The range of cells to be summed up if they meet the specified criteria.**criteria_range1**: The first range to be evaluated by the corresponding criteria.**criteria1**: The condition that must be met in the criteria_range1.**[criteria_range2, criteria2], …**: Additional ranges and their corresponding criteria. Up to 127 range/criteria pairs can be specified in SUMIFS.

Each criteria_range must be the same size and shape as sum_range. The function sums up the values in sum_range where the corresponding cells in each criteria_range satisfy the given criteria.

**Example**

For example, to sum the sales amounts in a specific region for a particular product:

`SUMIFS(total_sales, regions, "North", products, "Widget")`

In this example, *total_sales* is the range containing the sales amounts, *regions* is the range containing region names, “North” is the criterion for the region, *products* is the range containing product names, and “Widget” is the criterion for the product.

The SUMIFS function is extremely useful for complex data analysis tasks where you need to sum values based on multiple conditions, often used in financial, statistical, and various data analysis scenarios.

**Example 1: Calculate the sum of sales for a specific region and product**

Formula: =SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2)

Result: 300

**Example 2: Calculate the sum of sales for a specific month and product**

Formula: =SUMIFS(C2:C5,A2:A5,E2,B2:B5,F2)

Result: 100

**Example 3: Calculate the sum of sales for a specific range of dates and product**

Formula: =SUMIFS(C2:C7,A2:A7,”>”&A2,A2:A7,”<“&A6,B2:B7,B2)

Result: 150

**Example 4: Calculate the sum of sales for a specific range of numbers and product**

Formula: =SUMIFS(C2:C5, A2:A5, “>”&A2, A2:A5, “<“&A5, B2:B5, F2)

Result: 150

**Example 5: Calculate the sum of sales for multiple products and regions**

Formula: =SUM(SUMIFS(C2:C7, A2:A7,{“East”,”West”},B2:B7,”A”))

Result: 300

**Example 6: Calculate the sum of sales for a specific text string and product**

Formula: =SUMIFS(C2:C7, B2:B7, F2, A2:A7, E2)

Result: $220

**Example 7: Calculate the sum of sales for a specific date and text string**

Formula: =SUMIFS(C2:C8,B2:B8,F2,A2:A8,”>”&A3)

Result: 500

**Example 8: Calculate the sum of sales for blank cells and specific text string**

Formula: =SUMIFS(C2:C7,A2:A7,E2,B2:B7,””)

Result: 450

**Example 9: Calculate the sum of sales based on multiple criteria using logical operators**

Formula: =SUMIFS(D2:D8, A2:A8, “A”, B2:B8, “<20”, C2:C8, “>”&C3)

Result: 300

## SUMIF vs SUMIFS in Excel

### SUMIF Function

**Purpose:** Used to sum cells based on a single condition.

**Syntax:**

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

**range**: The range of cells to apply the criteria against.**criteria**: The condition that defines which cells will be added.**[sum_range]**: The actual cells to sum (optional).

**Example:**

`SUMIF(A1:A10, ">20", B1:B10)`

sums the values in B1:B10 where corresponding cells in A1:A10 are greater than 20.

### SUMIFS Function

**Purpose:** Used to sum cells based on multiple conditions.

**Example:**

`SUMIFS(B1:B10, A1:A10, ">20", C1:C10, "<50")`

sums the values in B1:B10 where corresponding cells in A1:A10 are greater than 20 and those in C1:C10 are less than 50.

### Key Differences

**Number of Criteria**: SUMIF evaluates one condition, while SUMIFS can evaluate multiple conditions.**Order of Arguments**: In SUMIF, the sum range is the third and optional argument. In SUMIFS, it is the first and mandatory argument.**Flexibility**: SUMIFS is more flexible and powerful, suitable for complex conditional summing scenarios.