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.

**Table of Contents**Expand

## Excel SUMIFS Function Overview

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 criterion to be checked in the criteria_range2.

In this tutorial, we will demonstrate 6 useful examples of the Excel **SUMIFS** function with multiple sum ranges and multiple criteria. In most of the examples, we will use the dataset (**B4:H11**) below which 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.

**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: **How to Use SUMIFS Function with Multiple Sheets in Excel

## 2. Including Dates in the SUMIFS Function with Multiple Sum Ranges and 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.

## 3. Array Argument for Multiple Sum Ranges and 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 for**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.

## 4. Using 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.

## 5. Utilizing 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 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 typing dynamic criteria (
**Countries**). You will see it as a 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.

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

Finally, we will apply **wildcard **characters with the **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.

**Read More:** How to Use SUMIFS Function with Wildcard in Excel

**Download Practice Workbook**

Download the practice workbook from here.

## 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.

## Related Articles

- SUMIFS with INDEX-MATCH Formula Including Multiple Criteria
- How to Use SUMIFS When Cells Are Not Equal to Multiple Text
- Excel SUMIFS Not Equal to Multiple Criteria
- [Fixed]: SUMIFS Not Working with Multiple Criteria

**<< Go Back to Excel SUMIFS with Multiple Criteria | Excel SUMIFS Function | Excel Functions | Learn Excel**