Table of Contents
VLOOKUP is a function in Google Sheets that allows you to search for a specific value in a column and return a corresponding value from a different column. To sum multiple rows using this function, you can use its optional “range_lookup” parameter to specify a range of cells to search within. This will enable you to retrieve values from multiple rows and then use the SUM function to add them up. For example, you can use VLOOKUP to search for a specific product in a sales data table and then sum up all the sales for that product from different regions or time periods. This allows you to quickly and easily calculate totals and analyze data in your Google Sheets.
You can use the following formulas to perform a VLOOKUP and sum multiple rows in Google Sheets:
Method 1: VLOOKUP and SUM Values in First Matched Row
=ARRAYFORMULA(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 Google Sheets 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:
=ARRAYFORMULA(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 74, 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 looks up “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 123 points across the two rows he appeared in.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets: