Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

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

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

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.

Include Dates in SUMIFS Function with Multiple Sum Ranges & Criteria

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.

Array Argument for Multiple Sum Ranges & Criteria with SUMIF & SUMIFS Functions

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.

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

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.

Use SUMIFS Function for Dynamic Criteria with Named Range

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.

Use SUMIFS Function for Dynamic Criteria with Named Range

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

Apply Wildcard Character with SUMIFS Function for Multiple Ranges & Criteria

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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sagufta Tarannum
Sagufta Tarannum

Sagufta Tarannum, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, contributes significantly as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep interest in research and innovation, she actively engages with Excel. In her role, Sagufta not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, underscoring her unwavering commitment to consistently delivering exceptional content. Her interests are Advanced... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo