SUMIFS with INDEX-MATCH Formula Including Multiple Criteria

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.


SUMIFS with INDEX-MATCH Multiple Criteria Formula: 6 Examples

For the demonstration purpose, we are going to use the below dataset. To avoid any version or compatibility issues, try to use the Excel 365 version.

sumifs with index match multiple criteria formula


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.

SUMIFS with INDEX-MATCH Combining Multiple Criteria

  • 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 criterion, it will move to the second criterion. 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.

Using SUMIFS with INDEX-MATCH Excluding Blank Cells

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


3. Combining Multiple SUMIFS with INDEX-MATCH Using OR Logic

Multiple criteria have 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.

Combining Multiple SUMIFS with INDEX-MATCH Using OR Logic

  • 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 “HPat 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 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: 


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.

SUMIFS with INDEX-MATCH for Array Argument

  • 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 criterion, it will move to the second criterion. 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.


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 is mentioned in cell F18.

SUMIFS with INDEX-MATCH for Dates

  • 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 criterion, it will move to the second criterion. The criteria here are data-related. If the values are after the date mentioned in cell F18, then they are going to be summed.


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.

SUMIFS with INDEX-MATCH Using Comparison Operator

  • 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 10,000 values.
  • If all of the criteria mentioned in the range of cells F16:F19 are 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 criterion, it will move to the second criterion. Next, we got the values, we now need to add values only that are over 10,000. The last part of the formula ensures this.


Download Practice Workbook

Download this practice workbook below.


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.


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo