Zahid Hasan
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.
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 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.
- 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.
=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.
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.
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.
Read More: Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria
Similar Readings
- Excel SUMIFS with Multiple Vertical and Horizontal Criteria
- How to Use SUMIFS Function in Excel with Multiple Criteria
- Exclude Multiple Criteria in Same Column with SUMIFS Function
- How to Use VBA Sumifs with Multiple Criteria in Same Column
- [Fixed]: SUMIFS Not Working with Multiple Criteria (3 Solutions)
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.
=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.
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
- Excel SUMIFS Not Equal to Multiple Criteria (4 Examples)
- How to Use SUMIFS Formula with Multiple Criteria in Excel (11 Ways)
- SUMIFS Sum Range Multiple Columns in Excel(6 Easy Methods)
- How to Use SUMIFS with Date Range and Multiple Criteria (7 Quick Ways)
- SUMIFS Multiple Criteria Different Columns (6 Effective Ways)
- SUMIF with Multiple Criteria in Column & Row in Excel (Both OR and AND Type)
