Table of Contents
VLOOKUP is a powerful function in Excel that allows users to quickly search and retrieve data from a specific column in a table. In addition, it can also be used to perform calculations, such as summing multiple rows of data. To use VLOOKUP to sum multiple rows, the function can be nested within the SUM function. This will allow the user to specify the range of cells to be summed, using the VLOOKUP function to determine the specific rows to be included. By using this method, users can efficiently and accurately sum multiple rows of data in Excel.
Excel: Use VLOOKUP to Sum Multiple Rows
You can use the following formulas to perform a VLOOKUP and sum multiple rows in Excel:
Method 1: VLOOKUP and SUM Values in First Matched Row
=SUM(VLOOKUP(A14, $A$2:$D$11, {2,3,4}, FALSE))
This particular formula sums the values in columns 2, 3, and 4 in the first row of the range A2:D11 where the value in column A is equal to the value in cell A14.
Method 2: VLOOKUP and SUM Values in All Matched Rows
=SUMPRODUCT((A2:A11=A14)*B2:D11)
This particular formula sums the values in columns B, C, and D for each row where the value in column A is equal to the value in cell A14.
The following examples show how to use each method in practice with the following dataset in Excel that shows the points scored by various basketball players in three different games:
Example 1: VLOOKUP and Sum Values in First Matched Row
We can type the following formula into cell B14 to sum the points values scored by Chad in all three games:
=SUM(VLOOKUP(A14, $A$2:$D$11, {2,3,4}, FALSE))
Once we press Enter, the results will be shown:
This formula uses a VLOOKUP to find Chad” in the Player column and then returns the sum of the points values for each game in the first row that matches Chad.
We can see that the formula returns a value of 40, which is the sum of the points scored by Chad in the first row where Chad” appears.
Example 2: VLOOKUP and Sum Values in All Matched Rows
=SUMPRODUCT((A2:A11=A14)*B2:D11)
Once we press Enter, the results will be shown:
This formula uses a VLOOKUP to find Chad” in the Player column and then returns the sum of the points values for each game in each row that matches Chad.
We can see that Chad scored a total of 102 points across the two rows he appeared in.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I use VLOOKUP to sum multiple rows in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-to-sum-multiple-rows-in-excel/
stats writer. "How can I use VLOOKUP to sum multiple rows in Excel?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-to-sum-multiple-rows-in-excel/.
stats writer. "How can I use VLOOKUP to sum multiple rows in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-to-sum-multiple-rows-in-excel/.
stats writer (2024) 'How can I use VLOOKUP to sum multiple rows in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-to-sum-multiple-rows-in-excel/.
[1] stats writer, "How can I use VLOOKUP to sum multiple rows in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use VLOOKUP to sum multiple rows in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.