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.