You can use the following methods to use the **SUM** function with **INDEX** and **MATCH** in Excel:

**Method 1: Use SUM with INDEX MATCH Based on Column Value**

=SUM(INDEX(A2:D6, 0, MATCH(F2,A1:D1,0)))

This particular formula will sum all of the values in the column where the column value among the range **A1:D1** is equal to the value in cell **F2**.

**Method 2: Use SUM with INDEX MATCH Based on Row and Column Values**

=SUMIF(B2:B9, G2, INDEX(C2:E9,0,MATCH(H2,C1:E1,0)))

This particular formula will sum the cells where the column value among the range **C1:E1** is equal to the value in cell **H2** and where the row value among the range **B2:B9** is equal to the value in cell **G2**.

The following examples show how to use each method in practice.

**Example 1: Use SUM with INDEX MATCH Based on Column Value**

Suppose we have the following dataset in Excel that shows the total sales of various fruits at a store during specific months:

Now suppose we would like to calculate the sum of all sales for the column where the fruit is equal to **Bananas**.

To do so, we can type the following formula into cell **G2**:

=SUM(INDEX(A2:D6, 0, MATCH(F2,A1:D1,0)))

The following screenshot shows how to use this formula in practice:

The formula returns a value of **26**.

We can verify that this is correct by manually calculating the sum of sales for each month for the Bananas column:

Sum of Sales for Bananas: 5 + 5 + 4 + 5 + 7 = **26**.

This matches the value calculated by the formula.

**Example 2: Use SUM with INDEX MATCH Based on Column Value**

Suppose we have the following dataset in Excel that shows the total sales of various fruits by store location and by month:

Now suppose we would like to calculate the sum of all sales for the column where the fruit is equal to **Bananas** and for the rows where the month is equal to **January**.

To do so, we can type the following formula into cell **I2**:

=SUMIF(B2:B9, G2, INDEX(C2:E9,0,MATCH(H2,C1:E1,0)))

The following screenshot shows how to use this formula in practice:

The formula returns a value of **12**.

We can verify that this is correct by manually calculating the sum of sales for each cell where the row is equal to **January** and the column is equal to **Bananas**:

Sum of Sales for Bananas in January: 5 + 7 = **12**.

This matches the value calculated by the formula.

**Additional Resources**

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Use INDEX and MATCH to Return Multiple Values Vertically

Excel: How to Use VLOOKUP to Return Multiple Columns

Excel: How to Use VLOOKUP to Return All Matches

This is great. What if I wanted to calculate how much total fruit was sold in the past 3 months ending in April (i.e. Feb, Mar, Apr)? Ideally the ending month be dynamic…meaning if I changed to March it would sum Jan, Feb, Mar. I’m assuming the month name is unique in my dataset.

Nice! It works. Thank you.