How to Easily Sum Multiple Rows with VLOOKUP in Excel

We will explore two primary methodologies for performing a conditional sum using a lookup value, addressing both the horizontal aggregation of the first match and the vertical aggregation of all matches:

Method 1: VLOOKUP and SUM Values in First Matched Row (Horizontal Summation)

=SUM(VLOOKUP(A14, $A$2:$D$11, {2,3,4}, FALSE))

This formula utilizes an Array Formula structure to sum 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 criteria specified in cell A14. This is ideal for aggregating data across adjacent columns for a unique record instance.

Method 2: SUMPRODUCT for Vertical Summation (All Matched Rows)

=SUMPRODUCT((A2:A11=A14)*B2:D11)

This alternative formula, utilizing the robust SUMPRODUCT function, sums the values in columns B, C, and D for each row where the value in column A is equal to the lookup criteria in cell A14. This method is preferred when the dataset contains duplicate entries and a grand total across all instances is required.

Prerequisite Dataset Overview

The following examples demonstrate how to apply each method in practice using a sample dataset in Excel. This data illustrates the points scored by various basketball players across three different games (Game 1, Game 2, and Game 3), highlighting instances where player names are duplicated:

Example 1: Horizontal Summation Using VLOOKUP and SUM (First Match Only)

In this scenario, we wish to find the total points scored by “Chad” across all three games, but critically, we only want the sum based on the first time “Chad” appears in the data table. This requires integrating the standard VLOOKUP function with the SUM function, transforming it into an Array Formula via the column index parameter.

We type the following formula into cell B14, assuming the lookup value (“Chad”) is in cell A14 and the data range is $A$2:$D$11:

=SUM(VLOOKUP(A14, $A$2:$D$11, {2,3,4}, FALSE))

This formula instructs VLOOKUP to return an array containing the scores from columns 2, 3, and 4 (Game 1, Game 2, Game 3) corresponding to the first match found for the value in A14.

Interpreting the Results of the SUM(VLOOKUP) Array

Once we press Enter (or Ctrl+Shift+Enter for older Excel versions), the calculated results reflecting the horizontal sum will be shown:

This configuration uses VLOOKUP to locate “Chad” in the Player column (column A) and returns the sum of the points values for each game found in the first row that matches “Chad.”

We can observe that the formula returns a value of 40, which is the sum of the points scored by Chad in the first row where he appears (20 + 10 + 10). Subsequent entries for Chad are intentionally ignored by this method.

Example 2: Vertical Summation Using SUMPRODUCT (All Matches)

If the goal is to aggregate all scores for “Chad,” regardless of how many times his name appears in the dataset, we must employ the SUMPRODUCT function. This function performs the necessary conditional filtering and summation across the entire vertical range of data.

We input the following formula, which creates a boolean array to check for the player’s name and multiplies it by the corresponding scores:

=SUMPRODUCT((A2:A11=A14)*B2:D11)

The first part, (A2:A11=A14), generates an array of TRUEs and FALSEs based on the match condition. Multiplying this array by the data range B2:D11 converts TRUEs to 1s and FALSEs to 0s, thereby isolating only the scores relevant to the lookup value.

Interpreting the Results of the SUMPRODUCT Calculation

Once we press Enter, the results reflecting the comprehensive vertical summation across all matches will be displayed:

This robust formula uses array multiplication logic to find all instances of “Chad” in the Player column and then returns the sum of the points values for each game in each row that matches the criteria.

We can see that Chad scored a total of 102 points. This figure accurately represents the sum of points from his first appearance (40 points) and his second appearance (62 points), demonstrating the power of SUMPRODUCT for handling conditional aggregation across duplicated data records.

Choosing the Appropriate Aggregation Method

The decision between the two methods hinges on whether you need a horizontal sum of related columns in a single row or a vertical sum across all matching rows in the dataset.

• Use SUM(VLOOKUP({…})) when you are certain the first match is the only data point that should be aggregated, and the summation needs to occur across adjacent columns.

• Use SUMPRODUCT when the source data contains multiple entries for the lookup value and the final output must be the grand total of all corresponding numerical cells. SUMPRODUCT is generally considered more flexible and powerful for multi-criteria summing tasks.