# How to Apply the SUMIFS with the INDEX MATCH Functions to Multiple Columns and Rows – 5 Easy Steps

The dataset showcases the Monthly Sales Data of the ABC Company for various Products and for 3 Sales Persons. You want to find the Sales of a Sales Person based on the Month and Product.

### Step 1 – Creating a Drop-Down List to Select the Month

• Create a cell named Month.

• Select the cell beside Month. Here, C18.
• Go to the Data tab.
• Choose Data Validation in Data Tools.

The Data Validation dialog box will open.

• Click the drop-down icon.
• Choose List.

• Click Source.
• Select the range \$D\$4:\$H\$4.

\$D\$4:\$H\$4, indicates the names of the Months.

• Click OK.

A drop-down icon will be displayed in C18.

### Step 2 – Checking the Drop-Down Button

• Click the drop-down icon beside C18.
• The name of the Months will be displayed in the drop-down. Choose Apr-22 (April-2022).

The name of the Month will be displayed in C18.

### Step 3 – Creating an Output Table

• Create a table.

• Enter the following formula in B22.
`=UNIQUE(B5:B16)`

B5:B16 refers to the cells of the Sales Person column.

• Press ENTER.

You will see unique names from the Sales Person column.

• Use the formula  below in C21.
`=TRANSPOSE(UNIQUE(C5:C16))`

C5:C16 represents the cells in the Product column.

• Press ENTER.

You will see the unique names of the Products in rows.

### Step 4 – Using the SUMIFS Function with the INDEX-MATCH Functions

• Enter the following formula in 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)`

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

Formula Breakdown

• MATCH(\$C\$18,\$D\$4:\$H\$4,0) → gives the relative position of data in an array that matches a specific value.
• \$C\$18 → is the lookup_value argument.
• \$D\$4:\$H\$4 → is the lookup_array argument.
• 0 →  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 → refers to the array argument.
• 4 →  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) →  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} → represents the sum_range argument.
• \$B\$5:\$B\$16 → is the criteria_range1 argument.
• \$B22 → refers to the criteria1 argument.
• \$C\$5:\$C\$16 → is the criteria_range2 argument.
• C\$21 → indicates the criteria2 argument.
• Output → \$8,177
• Press ENTER.

This is the output.

• Drag the Fill Handle to F22 to see the Sales for Jimmy in Apr-22.

• Use the AutoFill to see the Sales for Harry and Sarah.

### Step 5 – Checking if the Output is dynamic.

• Click the drop-down icon beside C18.
• Choose a month other than Apr-22 (April-2022). Here, Feb-22 (Februrary-2022).

The output will change automatically.

## How to Use the SUMPRODUCT with INDEX and MATCH Functions in Excel

Steps:

• Select a Month from the drop-down list. Here, March.

This is the output.

• Create a table.

• Select the cells under Name.
• Go to the Data tab.
• Choose Data Validation in Data Tools.

• In the Data Validation dialog box, choose List.
• Click Source.
• Select the cells in the Sales Person column.
• Click OK.

A drop-down icon will be displayed beside the Name column.

• Click it and choose a Name. Here, Sarah.

Choose 2 other names.

• Enter the following formula in C21.
`=SUMPRODUCT(INDEX(\$D\$5:\$H\$16,MATCH(B21,\$B\$5:\$B\$16,0),MATCH(\$C\$18,\$D\$4:\$H\$4,0)))`

B21 refers to the cell in the Name column.

Formula Breakdown

• MATCH(B21,\$B\$5:\$B\$16,0) → gives the relative position of data in an array that matches a specific value.
• B21 → is the lookup_value argument.
• \$B\$5:\$B\$16 → represents the lookup_array argument.
• 0 → 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)) → becomes INDEX(\$D\$5:\$H\$16,3,4).
• \$D\$5:\$H\$16 → is the array argument.
• 3 → indicates the row_num argument.
• 4 → 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))) → becomes SUMPRODUCT(5634).
• Output \$5,634.
• Press ENTER.

This is the output.

• Use the AutoFill.

If you change the Name, the output will change.

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

Practice here.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

1. Can I incorporate the total number of YTD? Suppose the total YTD for only of June

Mahfuza Anika Era Jul 25, 2023 at 12:51 PM

Dear Yosh,
Thank you for your query. Yes, you can determine the sum of YTD number. Firstly create a table like the following for each Month’s Sales of “Jimmy” for “Laptop”.
Copy this formula in cell C21.
`=SUMIFS(INDEX(\$D\$5:\$I\$16,,MATCH(B21,\$D\$4:\$I\$4,0)),\$B\$5:\$B\$16,\$B\$20,\$C\$5:\$C\$16,C\$20)`

You will get the sales for each month.
Next, to calculate the “YTD Grandtotal” copy this formula in cell C27.
`=SUM(C21:C26)`

I hope this method will solve your problem. Thank you!
Mahfuza Anika Era
ExcelDemy

2. If I want to calculate the subtotal of YTD? For example, the total YTD of Jimmy for laptop as of May