Rubayed Razib Suprov
Summation is a kind of part and parcel that Excel uses. SUMIFS function made this process more dynamic by using many criteria. If you are curious to know how you can do summation with SUMIFS with index match multiple criteria formula, then this article may come in handy for you. In this article, we discuss how you can do summation with SUMIFS with index match multiple criteria formula with elaborate explanations.
Download Practice Workbook
Download this practice workbook below.
6 Examples of Using SUMIFS with INDEX-MATCH Formula Including Multiple Criteria in Excel
For the demonstration purpose, we are going to use the below dataset. For avoiding any version or compatibility issues, try to use the Excel 365 version.
1. SUMIFS with INDEX-MATCH Combining Multiple Criteria
Here the summation of values will be done by combining multiple criteria. We are going to use functions like INDEX, MATCH, and SUMIFS.
Steps
- In the dataset presented below, we got the data of devices sold each month for the different devices from different brands.
- In this procedure, we are going to estimate the sale value of different criteria.
- The criteria are presented below.
- Then select cell F19 and enter the following formula:
=SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,F17,C5:C14,F18)
Entering this formula would estimate the summation of the sales values if the criteria below mentioned are fulfilled.
Formula Breakdown
MATCH(F16,D4:I4,0)
⮚ This formula will search for the values mentioned in cell F16 in the range of cell D4:I4. And return the column rank in that list.
INDEX(D5:I14,0,MATCH(F16,D4:I4,0))
⮚ This function will return the cell address of all the rows in the column returned in the MATCH formula, in the mentioned range cells D5:I14. The output, in this case, is H5:H14.
SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,F17,C5:C14,F18)
⮚ This formula will provide the summation of the range value returned by the INDEX formula. But they also follow some criteria. They would sum value in the range only if the F17 value is present in the range of cells B5:B14. If the value is not present in any row, the corresponding row value in the range of cell H5:H14 will be ignored. After it satisfies the first criteria, it will move to the second criteria. It will be for the value of F18 in the range of cell C5:C18.
They would sum value in the range of H5:H14 only if the F18 value is present corresponding in the range of cells C5:C14.
Read More: Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria
2. Using SUMIFS with INDEX-MATCH Excluding Blank Cells
We will be able to avoid the blank cells in excel while summation of values. We are going to use functions like INDEX, MATCH, and SUMIFS.
Steps
- In some instances, we have blank cells in the worksheet.
- We need to set up the formula in such a way that it will avoid the blank cells and calculate the rest of the non-blank values.
- In this case, we got a blank cell the cell H9.
- Select cell F19 and enter the following formula:
=SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,"<>",C5:C14,"<>")
- Entering this formula will estimate the summation of the values maintaining all the criteria mentioned in the range of cells. At the same time, it will also avoid blank cells
Formula Breakdown
MATCH(F16,D4:I4,0)
⮚ This formula will search for the values mentioned in cell F16 in the range of cell D4:I4. And return the column rank in that list.
INDEX(D5:I14,0,MATCH(F16,D4:I4,0))
⮚ This formula will return the cell address of all the rows in the column returned in the MATCH formula, in the mentioned range cells D5:I14. The output, in this case, is H5:H14.
SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,”<>”,C5:C14,”<>”)
⮚ This formula will provide the summation of the range value returned by the INDEX formula. The summation will occur if there is no blank cell in the range of cells B5:B14 and C5:C14. The “<>” would set the argument in this way.
Read More: How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows
3. Combining Multiple SUMIFS with INDEX-MATCH Using OR Logic
Multiple criteria are now become easy by sectioning the formula and combining them together. We are going to use functions like INDEX, MATCH, and SUMIFS for connecting the sections.
Steps
- Here, we go over the sales record of the devices across different months for different devices from different brands.
- We also have some criteria also mentioned in the range of cells F16:F18.
- Select cell F18 and enter the following formula:
=SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,"HP",C5:C14,F17)+SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,"Acer",C5:C14,F17)
- Entering this formula adds two separate criteria values at the same time.
- It will evaluate and sum for brands “Acer” and “HP” at the same time.
- Using this method, you can use two separate criteria compared to only one criterion in the previous methods.
Formula Breakdown
MATCH(F16,D4:I4,0)
⮚ This formula will search for the values mentioned in cell F16 in the range of cell D4:I4. And return the column rank in that list.
INDEX(D5:I14,0,MATCH(F16,D4:I4,0))
⮚ This formula will return the cell address of all of the rows in the column returned in the MATCH formula, in the mentioned range cells D5:I14. The output, in this case, is H5:H14.
SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,”HP”,C5:C14,F17)+SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,”Acer”,C5:C14,F17)
⮚ These two parts actually indicate the same structure. The first part before the + sign indicates that the summation of values from the range returned from the INDEX formula if “HP” is present in the range of cell C5:C14. After the plus sign, the structure remains the same, just the HP is replaced with Acer. Both section values are then added together.
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 Sum Range Multiple Columns in Excel(6 Easy Methods)
- SUMIFS with Multiple Criteria Along Column and Row in Excel
- SUMIFS Multiple Criteria Different Columns (6 Effective Ways)
- Excel SUMIFS Not Equal to Multiple Criteria (4 Examples)
4. SUMIFS with INDEX-MATCH for Array Argument
This method will allow us to use multiple criteria through the array arrangement. We are going to use formulas like INDEX, MATCH, SUMIFS, and SUM for this method.
Steps
- We will use multiple criteria for the summation calculation and then we are going to add and sum them together.
- This method will give us the opportunity to sum multiple values from multiple criteria.
- For example, we so far calculated sales value from only criteria,
- Using this method, we can take multiple criteria effortlessly.
- Select cell F18, and enter the following formula:
=SUM(SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,{"HP","Acer","Dell"},C5:C14,F17))
- This formula will calculate the summation of device sales values for multiple brands.
Formula Breakdown
MATCH(F16,D4:I4,0)
⮚ This formula will search for the values mentioned in cell F16 in the range of cell D4:I4. And return the column rank in that list.
INDEX(D5:I14,0,MATCH(F16,D4:I4,0))
⮚ This formula will return the cell address of all of the rows in the column returned in the MATCH formula, in the mentioned range cells D5:I14. The output, in this case, is H5:H14.
SUM(SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,{“HP”,”Acer”,”Dell”},C5:C14,F17))
⮚ This formula will provide the summation of the range value returned by the INDEX formula. But, they also follow some criteria. They would sum value in the range only if the “HP”, “Acer”, and “Dell” value is present in the range of cells B5:B14. If the value is not present in any row, the corresponding row value in the range of cell H5:H14 will be ignored. After it satisfies the first criteria, it will move to the second criteria. It will be for the value of F18 in the range of cell C5:C18. They would sum value in the range of H5:H14 only if the F18 value is present corresponding in the range of cells C5:C14.
Read More: How to Use SUMIFS Function in Excel with Multiple Criteria
5. SUMIFS with INDEX-MATCH for Dates
Specific dates or period is going to be used as the criteria in this procedure. Only values after a certain date will count. We are going to use functions like INDEX, MATCH, and SUMIFS.
Steps
- We need to calculate the sales value of devices from different brands after a certain date.
- At the same time, we also need to fulfill some criteria also. They are mentioned in the range of cells F16:F19.
- The date after which we want to sum the values are mentioned in cell F18.
- Then select cell F19 and enter the following formula:
=SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,F17,C5:C14,">="&F18)
Entering this formula will estimate the summation of the sales values if they maintain the criteria. Also after the dates mentioned in cell F18.
Formula Breakdown
MATCH(F16,D4:I4,0)
⮚ This formula will search for the values mentioned in cell F16 in the range of cell D4:I4. And return the column rank in that list.
INDEX(D5:I14,0,MATCH(F16,D4:I4,0))
⮚ This formula will return the cell address of all the rows in the column returned in the MATCH formula, in the mentioned range cells D5:I14. The output, in this case, is H5:H14.
SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,F17,C5:C14,F18)
⮚ This formula will provide the summation of the range value returned by the INDEX function. But, they also follow some criteria. They would sum value in the range only if the F17 value is present in the range of cells B5:B14. If the value is not present in any row, the corresponding row value in the range of cell H5:H14 will be ignored. After it satisfies the first criteria, it will move to the second criteria. The criteria here are data related. If the values are after the date mentioned in cell F18, then they are going to be summed.
Read More: How to Use SUMIFS with Date Range and Multiple Criteria (7 Quick Ways)
6. SUMIFS with INDEX-MATCH Using Comparison Operator
We will use a comparison operator for multiple criteria compared to the previous methods. We are going to use functions like INDEX, MATCH, and SUMIFS.
Steps
- We need to calculate the sales value of devices from different brands using a comparison operator.
- At the same time, we also need to fulfill some criteria also. They are in the range of cells F16:F19.
- This comparison operator will help us to estimate the summation of sales value above a certain value, fulfilling the criteria.
- Then select cell F19 and enter the following formula:
=SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,F17,INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),">=10000")
- Entering this formula will estimate the summation of the sales value of products above 10000 values.
- If all of the criteria mentioned in the range of cell F16:F19 is maintained, then only two cells, cell H9, and H14 are supposed to be added together.
- But as we set a limit of 10000 inside the formula, only the H14 cell will be counted, and H9 will be discarded.
Formula Breakdown
MATCH(F16,D4:I4,0)
⮚ This formula will search for the values mentioned in cell F16 in the range of cell D4:I4. And return the column rank in that list.
INDEX(D5:I14,0,MATCH(F16,D4:I4,0))
⮚ This formula will return the cell address of all of the rows in the column returned in the MATCH formula, in the mentioned range cells D5:I14. The output, in this case, is H5:H14.
SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,F17,INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),”>=10000″)
⮚ This formula will provide the summation of the range value returned by the INDEX formula. But, they also follow some criteria. They would sum value in the range only if the F17 value is present in the range of cells B5:B14. If the value is not present in any row, the corresponding row value in the range of cell H5:H14 will not count. After it satisfies the first criteria, it will move to the second criteria. Next, we got the values, we now need to add values only that are over 10000. The last part of the formula ensures this.
Read More: [Fixed]: SUMIFS Not Working with Multiple Criteria (3 Solutions)
Conclusion
To sum it up, how you can do summation with SUMIFS functions with INDEX–MATCH formula including multiple criteria in excel is answered here by 6 different examples.
For this problem, a workbook is available to download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the ExcelDemy community will be highly appreciable.
