We often use the **SUMIFS function for multiple criteria** in Excel. But in this article, we will use the Excel **SUMIFS** function with **multiple sum ranges** and **multiple criteria**. Here, we will demonstrate **6** examples with explanations.

## Download Practice Workbook

Download the practice workbook from here.

## Introduction to Excel SUMIFS Function

We use the** SUMIFS **function to **sum** up the values in the cells that satisfy various criteria like **dates**, **numbers** and **text**. Moreover, we can use the **comparison operators** and **wildcards** in this function to match the data **partially**.

**Syntax**

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

**Arguments**

* sum_range:* It is the range that we want to sum up.

* criteria_range1:* It is the first range that we need to evaluate by the corresponding criteria.

* criteria1: *It is the first criteria that we want to check in the criteria_range1.

* criteria_range2:* It is the second range to be evaluated.

* criteria2:* It is the first criteria to be checked in the criteria_range2.

## 6 Useful Examples of Excel SUMIFS Function with Multiple Sum Ranges and Multiple Criteria

In this tutorial, we will demonstrate **6** useful examples of Excel **SUMIFS** function with **multiple sum ranges** and **multiple criteria**. In most of the examples, we will use the dataset (**B4:H11**) below that contains two tables. Here, we have added the two tables for inserting **multiple sum ranges** in the **SUMIFS** function. However, the tables contain some **Countries**, **Fruits** and their corresponding **Sales**. Let’s see the application of the **SUMIFS** function in the dataset to sum up the values with **multiple sum ranges** and **multiple criteria**.

### 1. Multiple Sum Ranges & Criteria with Excel SUMIFS Function Using Comparison Operators

In the first example, we will use the **SUMIFS** function with **comparison or logical **(**>, <, =**) **operators** to sum up the values in **multiple ranges** satisfying **multiple criteria**. To demonstrate this example, we will use the dataset (**B4:H11**) below. Here, we will sum up the **Sales** based on the **Country** and the **Sales** value. Let’s see the steps below to do so.

**Steps:**

- In the first place, go to the
**cell**where you want to insert the result (cell**C13**in our case). - Secondly, to sum up the
**Sales**values, type the formula:

`=SUMIFS(D5:D11,B5:B11,"USA",D5:D11,">=1500")+SUMIFS(H5:H11,F5:F11,"Canada",H5:H11,">=1300")`

- Therefore, we can see the formula in the
**Formula Bar**of the image below.

- After inserting the formula, press the
**Enter**key to get the result. - Finally, we can see the output in cell
**C13**of the screenshot below.

**🔎**** How Does the Formula Work?**

**D5:D11 & H5:H11 (first part):**Indicate the**sum_range**.**B5:B11 & F5:F11:**Denote the**criteria_range1**.**“USA” & “Canada”:**Refer to the**criteria1**.**D5:D11 & H5:H11 (last part):**Denote the**criteria_range2**.**“>=1500” & “>=1300”:**Refer to the**criteria2**.**SUMIFS(D5:D11,B5:B11,”USA”,D5:D11,”>=1500″):**Returns the summation of the**Sales**for**USA**with the**Sales**value greater than**$1500**.**SUMIFS(H5:H11,F5:F11,”Canada”,H5:H11,”>=1300″):**Adds the**Sales**values for**Canada**that are greater than**$1300**.**SUMIFS(D5:D11,B5:B11,”USA”,D5:D11,”>=1500″)+SUMIFS(H5:H11,F5:F11,”Canada”,H5:H11,”>=1300″):**Sums up the two values gained from the two**SUMIFS**formula.

**Read More:** **[Fixed]: SUMIFS Not Working with Multiple Criteria (3 Solutions)**

### 2. Include Dates in SUMIFS Function with Multiple Sum Ranges & Criteria

In this example, we will include **dates** in the **SUMIFS** function with multiple sum ranges & criteria. To describe this example, we will use the dataset (**B4:H11**) below containing the names of some **Fruits**, the **Order Date** of the fruits and their corresponding **Sales** value. Here, we want to add the **Sales** values depending on the **Fruits** and their **Order Date**. The steps to apply this example are below.

**Steps:**

- In the beginning, activate cell
**C13**to place the summation. - Then, insert the following formula in the cell (
**C13**) for adding the**Sales**values:

`=SUMIFS(D5:D11,B5:B11,"Apple",C5:C11,">="&TODAY()-10,C5:C11,"<="&TODAY())+SUMIFS(H5:H11,F5:F11,"Orange",G5:G11,">="&TODAY()-8,G5:G11,"<="&TODAY())`

- Now, see the formula in the
**Formula Bar**of the picture below.

- After that, press the
**Enter**key to find the result. - Lastly, see the output in cell
**C13**of the screenshot below.

**🔎**** How Does the Formula Work?**

**&TODAY:**Returns the**current date**.**SUMIFS(D5:D11,B5:B11,”Apple”,C5:C11,”>=”&TODAY()-10,C5:C11,”<=”&TODAY()):**Adds the**Sales**for**Apple**with the**Order Dates**within**10 days**before & including the**current date**.**SUMIFS(H5:H11,F5:F11,”Orange”,G5:G11,”>=”&TODAY()-8,G5:G11,”<=”&TODAY()):**Sums up the**Sales**for**Orange**with the**Order Dates**within**8 days**before & including the**current date**.**SUMIFS(D5:D11,B5:B11,”Apple”,C5:C11,”>=”&TODAY()-10,C5:C11,”<=”&TODAY())+SUMIFS(H5:H11,F5:F11,”Orange”,G5:G11,”>=”&TODAY()-8,G5:G11,”<=”&TODAY()):**Returns the summation of the two values gained from the two**SUMIFS**formula.

**Read More: ****How to Use SUMIFS with Date Range and Multiple Criteria (7 Quick Ways)**

### 3. Array Argument for Multiple Sum Ranges & Criteria with SUM & SUMIFS Functions

Suppose, we have a dataset (**B4:H11**) below containing some **Countries**, the **Fruits** they ordered and their respective **Sales**. Now, we want to **add** the **Sales** values based on the **Fruits** and **Countries**. Hence, we will use the **array argument** with **the SUM function** and **the SUMIFS function** in Excel. To do so, follow the steps below.

**Steps:**

- Firstly, select cell
**C13**. - Next, to add the
**Sales**values, insert the following formula in cell**C13**:

`=SUM((SUMIFS(D5:D11,C5:C11,"Apple",B5:B11,{"USA","France"}))+(SUMIFS(H5:H11,G5:G11,"Orange",F5:F11,{"Canada","Denmark"})))`

- Therefore, we can see the formula in the
**Formula Bar**of the image below. - After that, press
**Ctrl**+**Shift**+**Enter**(for**array formula**).

**🔎**** How Does the Formula Work?**

**SUMIFS(D5:D11,C5:C11,”Apple”,B5:B11,{“USA”,”France”}):**Returns two summations of the**Sales**of**Apple**. The first one is for the orders by the**USA**and the second one is by**France**.**SUMIFS(H5:H11,G5:G11,”Orange”,F5:F11,{“Canada”,”Denmark”}):**Returns two summations of the**Sales**of**Orange**. The first one is for the**USA**and the second one is for**France**.**SUM((SUMIFS(D5:D11,C5:C11,”Apple”,B5:B11,{“USA”,”France”}))+(SUMIFS(H5:H11,G5:G11,”Orange”,F5:F11,{“Canada”,”Denmark”}))):**Returns the summation of the**four**values obtained from the two**SUMIFS**formula.

- Finally, we can see the result in cell
**C13**of the screenshot below.

**Read More:** **How to Use SUMIFS Formula with Multiple Criteria in Excel (11 Ways)**

**Similar Readings**

**Excel SUMIFS with Multiple Vertical and Horizontal Criteria****SUMIFS with INDEX-MATCH Formula Including Multiple Criteria****How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows****How to Use VBA Sumifs with Multiple Criteria in Same Column****SUMIF vs SUMIFS in Excel (A Comparative Analysis)**

### 4. SUMIFS Function for Blank & Non-Blank Cells with Multiple Ranges & Criteria

Assuming, we have a dataset (**B4:J11**) below that contains the names of some **Countries**, the **Fruits** they ordered, the **Order Date** of the **Fruits** and their corresponding **Sales**. We can see that the dataset contains both **blank** and **non-blank **cells. Now, we will **sum up** the **Sales** values based on the **Countries** and the **blank** & **non-blank **cells in the **Fruits** and **Order Date** columns. Let’s see the following steps to do so.

**Steps:**

- Firstly, select the cell (
**C13**) in which you want to insert the summation. - After that, to get the summation of the
**Sales**, type the following formula:

`=SUMIFS(E5:E11,B5:B11,"USA",C5:C11,"<>",D5:D11,"=")+SUMIFS(J5:J11,G5:G11,"Canada",H5:H11,"<>",I5:I11,"=")`

- Lastly, get the result by pressing the
**Enter**key (see screenshot).

**🔎**** How Does the Formula Work?**

**“<>”:**Indicates the**non-blank**cells.**“=”:**Denotes the**blank**cells.**SUMIFS(E5:E11,B5:B11,”USA”,C5:C11,”<>”,D5:D11,”=”):**Adds the**Sales**for**USA**with the**non-blank**cells in the**C5:C11**range and the**blank**cells in the**D5:D11**range.**SUMIFS(J5:J11,G5:G11,”Canada”,H5:H11,”<>”,I5:I11,”=”):**Sums up the**Sales**for**Canada**with the**non-blank**cells in the**H5:H11**range and the**blank**cells in the**I5:I11**range.**SUMIFS(E5:E11,B5:B11,”USA”,C5:C11,”<>”,D5:D11,”=”)+SUMIFS(J5:J11,G5:G11,”Canada”,H5:H11,”<>”,I5:I11,”=”):**Returns the summation of the two values gained from the two**SUMIFS**formula.

**Read More:** **SUMIFS Sum Range Multiple Columns in Excel(6 Easy Methods)**

### 5. Use SUMIFS Function for Dynamic Criteria with Named Range

Here, we will use the **SUMIFS** function for **dynamic criteria** with a **named range**. For this, we will use the dataset (**B4:H11**) containing some **Countries**, **Fruits** and their respective **Sales** value. Moreover, we will use the two countries in the cells **C13** & **C14** to create the **dynamic criteria**. See the steps below to apply this example.

**Steps:**

- First, select the range
**C13:C14**to create the**dynamic criteria**. - Next, go to the
**Name Box**(see screenshot).

- Afterward, type any name (
**Countries**) in the**Name Box**for the**dynamic criteria**.

- Then, select cell
**C16**where you want to keep the output. - When you start inserting the formula and start typing
**dynamic criteria**(**Countries**) then you will see it as the suggestion (see screenshot). - Therefore, the formula is:

`=SUMIFS(D5:D11,B5:B11,Countries)+SUMIFS(H5:H11,F5:F11,Countries)`

- Finally, finish typing the formula to find the output.

- After pressing
**Ctrl + Shift + Enter**(for the older versions before**Excel 2019**), you will get the output in cells**C16**and**C17**. - Accordingly, the value in cell
**C16**is the summation of**Sales**for**Denmark**and the one in**C17**is for**France**.

**🔎**** How Does the Formula Work?**

**SUMIFS(D5:D11,B5:B11,Countries):**Adds the**Sales**for the range**B5:B11**based on the dynamic criteria (**Countries**).**SUMIFS(H5:H11,F5:F11,Countries):**Sums up the**Sales**for the range**H5:H11**based on the dynamic criteria named**Countries**.**SUMIFS(D5:D11,B5:B11,Countries)+SUMIFS(H5:H11,F5:F11,Countries):**Returns the summation of the two values gained from the two**SUMIFS**formula.

**Read More:** **SUMIF with Multiple Criteria in Column & Row in Excel (Both OR and AND Type)**

### 6. Apply Wildcard Character with SUMIFS Function for Multiple Ranges & Criteria

Finally, we will apply **wildcard characters** with **SUMIFS function** for multiple ranges and criteria. **Wildcard characters** are special types of **symbols** that are used to indicate one or more than one character. However, some examples of wildcard characters are **%** (percentage), ***** (asterisk), **?** (question mark), **[ ]** (open & close brackets) etc. In our case, we will use the asterisk (*****) symbol to represent a **letter**. For this example, we will use the dataset (**B4:H11**) below. Here, we will calculate the summation of the **Sales** values based on the **Countries** and a **letter** in the **Fruits**. We will represent the **letter** with the **asterisk** symbol. Let’s see the steps below to apply this example.

**Steps:**

- First of all, activate cell
**C13**. - Afterward, to find the summation, type the formula:

`=SUMIFS(D5:D11,B5:B11,"USA",C5:C11,"*s*")+SUMIFS(H5:H11,F5:F11,"Canada",G5:G11,"*g*")`

- In the end, press the
**Enter**key to get the result (see the screenshot below).

**🔎**** How Does the Formula Work?**

**SUMIFS(D5:D11,B5:B11,”USA”,C5:C11,”*s*”):**Adds the**Sales**for the**USA**with the**Fruits**containing ‘**s**’.**SUMIFS(H5:H11,F5:F11,”Canada”,G5:G11,”*g*”):**Sums up the**Sales**.**Canada**with the fruits containing ‘**g**’.**SUMIFS(D5:D11,B5:B11,”USA”,C5:C11,”*s*”)+SUMIFS(H5:H11,F5:F11,”Canada”,G5:G11,”*g*”):**Returns the summation of the two values gained from the two**SUMIFS**formula.

## Conclusion

I hope the above tutorial will be helpful for you to use the **SUMIFS **function in Excel with multiple sum ranges and multiple criteria. Download the practice workbook and give it a try. Let us know your feedback in the comment section. Follow our website **ExcelDemy** to get more articles like this.

## Related Articles

**Exclude Multiple Criteria in Same Column with SUMIFS Function****SUMIFS with Multiple Criteria Along Column and Row in Excel****SUMIFS Multiple Criteria Different Columns (6 Effective Ways)****SUMIFS Multiple Columns in Excel (5 Types of Application)****SUMIFS with Multiple Criteria in the Same Column (5 Ways)****Excel SUMIFS Not Equal to Multiple Criteria (4 Examples)**