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

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.

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

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.

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.

• Now, select the cell beside the newly created Month cell. In this case, it is cell C18.
• Following that, go to the Data tab.
• 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.

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

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.

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.

• Following that, enter the following formula in cell B22.

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.

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.

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.

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.

• Subsequently, press ENTER.

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

• Now, drag the Fill Handle along the row up to 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.

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.

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.

• Firstly, follow the steps mentioned in Step 01 of method 01 to enable the drop-down icon beside cell C18.

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

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

Similarly, choose 2 other names according to your preference.

• After that, enter the following formula in cell C21.

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

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

Download Practice Workbook

Conclusion

That’s all about today’s session. I strongly believe that this article was able to guide you to use the 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. Happy learning!

Related Articles

• Excel SUMIFS with Multiple Vertical and Horizontal Criteria
• How to Use SUMIFS with Multiple Criteria in the Same Column
• How to Apply SUMIFS with Multiple Criteria in Different Columns
• SUMIFS: Sum Range Across Multiple Columns
• Exclude Multiple Criteria in Same Column with SUMIFS Function
• How to Use VBA SUMIFS with Multiple Criteria in Same Column

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