Table of Contents
The SUM function in Excel allows users to easily add up a range of cells and retrieve the total value. When combined with the INDEX MATCH function, it can be used to search for a specific value within a range and then return the sum of a corresponding set of cells. This is particularly useful when dealing with large data sets and wanting to retrieve specific values based on certain criteria. The INDEX MATCH function acts as a lookup function, while the SUM function performs the calculation. By using these two functions together, users can efficiently and accurately retrieve the desired sum value in their Excel spreadsheets.
Excel: Use SUM with INDEX MATCH
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.
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.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I use the SUM function with INDEX MATCH in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-sum-function-with-index-match-in-excel/
stats writer. "How can I use the SUM function with INDEX MATCH in Excel?." PSYCHOLOGICAL SCALES, 23 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-sum-function-with-index-match-in-excel/.
stats writer. "How can I use the SUM function with INDEX MATCH in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-sum-function-with-index-match-in-excel/.
stats writer (2024) 'How can I use the SUM function with INDEX MATCH in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-sum-function-with-index-match-in-excel/.
[1] stats writer, "How can I use the SUM function with INDEX MATCH in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the SUM function with INDEX MATCH in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.