Sagufta Tarannum
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)
