How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows

Get FREE Advanced Excel Exercises with Solutions!

While working in Excel, we often need to do some conditional sums, and to do this we can use the SUMIFS function along with the combination of INDEX and MATCH functions for multiple columns and rows. Doing a conditional sum depending on multiple criteria can be a quite complicated task. But luckily we have Microsoft Excel. Excel can handle these situations without sweat. In this article, we will learn 5 easy steps to use the SUMIFS function with INDEX, and MATCH functions for Multiple Columns and Rows.


Download Practice Workbook


5 Easy Steps to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows

Let’s say, we have Monthly Sales Data of ABC Company for various Products and for 3 Sales Persons. We aim to find the Sales of a Sales Person based on the Month and Product using the  SUMIFS function with INDEX, and MATCH functions. Let’s follow the steps mentioned below to do this.

sumifs index match multiple columns and rows

Not to mention that we have used the Microsoft Excel 365 version for this article, you can use any other version according to your convenience

Step 01: Creating Drop-Down List to Select Month

In the first step, we will create a drop-down list to select the Month. Using this drop-down list we will be able to choose any Month and our outputs will be changed accordingly.

  • Firstly, create a cell named Month as marked in the following image.

Creating Drop-Down List to Select Month to Use SUMIFS with INDEX, and MATCH Functions for Multiple Columns and Rows

  • Now, select the cell beside the newly created Month cell. In this case, it is cell C18.
  • Following that, go to the Data tab from Ribbon.
  • Then, choose the Data Validation option from the Data Tools group.

Consequently, the Data Validation dialogue box will open on your worksheet.

  • Now, from the Data Validation dialogue box, click on the drop-down icon as marked in the following image.
  • After that, choose the List option from the drop-down.

Editing Data Validation dialogue bix to Use SUMIFS with INDEX, and MATCH Functions for Multiple Columns and Rows

  • Following that, click on the field named Source.
  • Then, select the range of cells $D$4:$H$4.

Here, the range of cells $D$4:$H$4, indicates the name of the Months for the year 2022.

  • Now, click on OK.

Subsequently, a drop-down icon will appear in cell C18 as shown in the image below.

Final output of step 01 to Use SUMIFS with INDEX, and MATCH Functions for Multiple Columns and Rows

Step 02: Checking the Drop-Down Button

Now, we will check that is our drop-down button is working correctly or not. To do this, let’s use the steps discussed here.

  • Firstly, click on the drop-down icon beside cell C18.
  • As a result, the name of the Months will be available in the drop-down. Then, choose the Apr-22 (April-2022) option from the drop-down. You can also choose any other Month as you wish.

Checking the Drop-Down Button to Use SUMIFS with INDEX, and MATCH Functions for Multiple Columns and Rows

Consequently, the name of the Month will be available in cell C18.

Step 03: Creating Output Table

Here, we will use the UNIQUE and TRANSPOSE functions of Excel. The UNIQUE function returns a set of unique cells from an array of cells. The TRANSPOSE function converts the columns into rows while keeping the data intact.

  • Firstly, create a table like shown in the image below.

Creating Output Table to Use SUMIFS with INDEX, and MATCH Functions for Multiple Columns and Rows

  • Following that, enter the following formula in cell B22.
=UNIQUE(B5:B16)

Here, the range of cells B5:B16 refers to the cells of the column named Sales Person.

  • Then, press ENTER.

As a result, you will have unique names from the cells of the column named Sales Person.

  • Following that, use the formula given below in cell C21.
=TRANSPOSE(UNIQUE(C5:C16))

Here, the range of cells C5:C16 represents the cells of the Product column.

  • After that, hit ENTER.

As a result, you will have the unique names of the Products in rows as shown in the following picture.

Final output of step 03 to Use SUMIFS with INDEX, and MATCH Functions for Multiple Columns and Rows

Step 04: Using the SUMIFS Function with INDEX-MATCH Functions

In this step, we will apply an Excel formula where we will use the SUMIFS function along with the combination of the INDEX and MATCH functions.

  • Firstly, enter the following formula in cell C22.
=SUMIFS(INDEX($D$5:$H$16,,MATCH($C$18,$D$4:$H$4,0)),$B$5:$B$16,$B22,$C$5:$C$16,C$21)

Here, $D$5:$H$16 refers to the Sales data for various Months of the ABC Company, $C$18 indicates the selected Month, $D$4:$H$4 represents the array of all Months, $B22 indicates the Name of the Sales Person, C$21 refers to the cell of selected Product.

Formula Breakdown

  • MATCH($C$18,$D$4:$H$4,0) → It gives us the relative position of data in an array that matches a specific value.
    • $C$18 → It is the lookup_value argument.
    • $D$4:$H$4 → This is the lookup_array argument.
    • 0 → This indicates the [match_type] argument.
    • Output → 4
  • INDEX($D$5:$H$16,,MATCH($C$18,$D$4:$H$4,0)) → It becomes INDEX($D$5:$H$16,,4)
    • $D$5:$H$16 → It refers to the array argument.
    • 4 → It is the column_num argument.
    • Output → {8177;5060;5634;6230;5130;8196;7250;6115;8431;5661;6265;7328}.
  • SUMIFS(INDEX($D$5:$H$16,,MATCH($C$18,$D$4:$H$4,0)),$B$5:$B$16,$B22,$C$5:$C$16,C$21) →  It becomes SUMIFS({8177;5060;5634;6230;5130;8196;7250;6115;8431;5661;6265;7328},$B$5:$B$16,$B22,$C$5:$C$16,C$21)
    • {8177;5060;5634;6230;5130;8196;7250;6115;8431;5661;6265;7328} → It represents the sum_range argument.
    • $B$5:$B$16 → It is the criteria_range1 argument.
    • $B22 → This refers to the criteria1 argument.
    • $C$5:$C$16 → It is the criteria_range2 argument.
    • C$21 → This indicates the criteria2 argument.
    • Output → $8,177
  • Subsequently, press ENTER.

Using the SUMIFS Function with INDEX-MATCH Functions to Use SUMIFS with INDEX, and MATCH Functions for Multiple Columns and Rows

As a result, you will have the following output on your worksheet.

  • Now, drag the Fill Handle along the row up to the cell F22 to get the Sales for Jimmy in the month of Apr-22 for different Products.

  • Finally, use the AutoFill feature of Excel to have the Sales data for Harry and Sarah as shown in the following image.

Read More: SUMIFS with INDEX-MATCH Formula Including Multiple Criteria

Step 05: Checking Dynamicity of Output

This is the final step of the process. In this step, we will check the dynamicity of the output. That means we will check that is our output changing dynamically with the change of the selected Month in cell C18.

  • Firstly, click on the drop-down icon beside cell C18.
  • After that, from the drop-down choose a different month other than Apr-22 (April-2022). In this case, we have selected Feb-22 (Februrary-2022).

Consequently, the outputs will be changed automatically and it will show the Sales Data for February as demonstrated in the following image.

Final output of step 05 to Use SUMIFS with INDEX, and MATCH Functions for Multiple Columns and Rows

Read More: Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria


Similar Readings


How to Use SUMPRODUCT with INDEX and MATCH Functions in Excel

In Excel, we often need to use the SUMPRODUCT function along with the combination of INDEX and MATCH functions in a wide range of situations. In this section of the article, we will learn how we can use it in different scenarios. Let’s follow the steps discussed in the following section.

Steps:

In the beginning, we will see how we can find the output based on 1 row and 1 column.

  • After that, select a Month from the drop-down list. Here, we chose the month of March.

As a result, you will have the following output on your worksheet.

  • Next, create a table as shown in the following image.

Creating output table to Use SUMPRODUCT with INDEX and MATCH Functions in Excel

  • After that, select the cells under the Name column of the newly created table.
  • Then, go to the Data tab from Ribbon.
  • Now, choose the Data Validation option from the Data Tools group.

  • Following that, in the Data Validation dialogue box, choose the List option.
  • Now, click on the field named Source.
  • After that, select the cells of the Sales Person column.
  • Subsequently, click on OK.

As a result, drop-down icons will be available beside the cells of the Name column.

  • Then, click one drop-down button and choose a Name from the list. Here, we have selected the name Sarah.

Selecting name to Use SUMPRODUCT with INDEX and MATCH Functions in Excel

Similarly, choose 2 other names according to your preference.

  • After that, enter the following formula in cell C21.
=SUMPRODUCT(INDEX($D$5:$H$16,MATCH(B21,$B$5:$B$16,0),MATCH($C$18,$D$4:$H$4,0)))

Here, cell B21 refers to the cell of the Name column.

Formula Breakdown

  • MATCH(B21,$B$5:$B$16,0) → It gives us the relative position of data in an array that matches a specific value.
    • B21 → It is the lookup_value argument.
    • $B$5:$B$16 → This represents the lookup_array argument.
    • 0 → It is the [match_type] argument.
    • Output 3.
  • MATCH($C$18,$D$4:$H$4,0) Output 4.
  • INDEX($D$5:$H$16,MATCH(B21,$B$5:$B$16,0),MATCH($C$18,$D$4:$H$4,0)) → It becomes INDEX($D$5:$H$16,3,4).
    • $D$5:$H$16 → It is the array argument.
    • 3 → This indicates the row_num argument.
    • 4 → It refers to the [column_num] argument.
    • Output 5634.
  • SUMPRODUCT(INDEX($D$5:$H$16,MATCH(B21,$B$5:$B$16,0),MATCH($C$18,$D$4:$H$4,0))) → It becomes SUMPRODUCT(5634).
    • Output $5,634.
  • Then, hit ENTER.

Subsequently, you will have the following output as marked in the following picture.

  • Finally, use the AutoFill option of Excel to get the remaining outputs.

You can change the selection of the Name by using the drop-down lists and your outputs will be changed accordingly as demonstrated in the image below.

You can also use the SUMPRODUCT function along with the combination of INDEX and MATCH functions for multiple rows and columns.

Read More: SUMIFS with Multiple Criteria Along Column and Row in Excel


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

Practice section to Use SUMIFS with INDEX, and MATCH Functions for Multiple Columns and Rows


Conclusion

That’s all about today’s session. I strongly believe that this article was able to guide you to use SUMIFS function with INDEX, and MATCH functions for multiple columns and rows in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website, ExcelDemy. Happy learning!


Related Articles

Zahid Hasan

Zahid Hasan

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo