Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel VLookup Sum Multiple Rows and Columns in 3 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

By J.A. Gomez J.A. Gomez from Power Spreadsheets

Excel VLookup Sum Multiple Rows and Columns in 3 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)In this Excel VLookup Sum Multiple Rows and Columns Tutorial, you learn how to:

  • Carry out a VLookup; and
  • Sum multiple columns in multiple rows (with matches).

The VLookup sum multiple rows and columns formula template/structure you learn in this Tutorial isn't the only way to:

  • Carry out a VLookup; and
  • Sum multiple row or column values.

You may (also) be interested in the following alternative formula templates/structures:

  • Excel VLookup Sum Multiple Row Values (in Same Column).
  • Excel VLOOKUP Sum Multiple Columns (Values).
  • Excel VLookup Sum Multiple Column Values (with XLOOKUP).

I link to these Tutorials in the Related Excel Training Materials and Resources Section below.

This Excel VLookup Sum Multiple Rows and Columns Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VLookup Sum Multiple Rows and Columns example workbook

This Excel VLookup Sum Multiple Rows and Columns Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

My Excel XLOOKUP Tutorial (click here to open) may help you:

  • Better understand and implement the contents below.
  • Better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

You can find more Excel Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials:

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link). The following are some of the topics covered in these Excel Courses:

  • Excel essentials and must-know skills.
  • Advanced Excel Formulas.
  • Excel Tables.
  • Pivot Tables.
  • Dashboards.
  • Power Pivot.
  • Power Query.

If you want to start learning how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).


Get immediate free access to the Excel VLookup Sum Multiple Rows and Columns example workbook

The VLookup Sum Multiple Rows and Columns Formula Template/Structure

The following is the VLookup sum multiple rows and columns formula template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-rows-columns/
=SUMPRODUCT((ColumnWhereYouSearchIn=LookupValue)*ColumnsWithValuesToAdd)


Get immediate free access to the Excel VLookup Sum Multiple Rows and Columns example workbook

The Example Before VLookup Sum Multiple Rows and Columns

This Excel VLookup Sum Multiple Rows and Columns Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VLookup Sum Multiple Rows and Columns example workbook

The example worksheet has 2 tables/sections with the following characteristics:

(1) Table 1 (Cells A6 to D26).

The table:

  • With the data.
  • Where you search with the VLookup sum multiple rows and columns example formula.

Main characteristics:

  • 4 columns (Salesperson, Units 1, Units 2, Units 3).
  • 1 header row (row 6).
  • 20 entries (rows 7 to 26).

(2) Table 2 (Cells F6 to H7).

The table where you:

  • Set up the VLookup sum multiple rows and columns example formula.
  • Display the results.

This table has the following 3 columns:

  • Salesperson: Cell F7 contains the lookup value.
  • Total Units: Cell G7:
    • Is currently empty.
    • Will store the VLookup sum multiple rows and columns example formula.
  • Formula: Cell H7:
    • Currently displays the #N/A error.
    • Will display the VLookup sum multiple rows and columns example formula I enter in cell G7.
VLookup sum multiple rows and columns before formula


Get immediate free access to the Excel VLookup Sum Multiple Rows and Columns example workbook

Step 1: Call the SUMPRODUCT Function

You (usually) carry out a VLookup with 1 of the following functions:

  • VLOOKUP; or
  • XLOOKUP.

However:

  • If the first/leftmost column in the table you look in with the VLOOKUP function contains duplicate values (and you look up one of those duplicate values), the VLOOKUP function works with the first entry matching the lookup value.
  • If the cell range you search in with the XLOOKUP function contains duplicate values (and you look up one of those duplicate values), the XLOOKUP function works (by default) with the first entry matching the lookup value.

Work with the SUMPRODUCT function to create a VLookup sum multiple rows and columns formula.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-rows-columns/
=SUMPRODUCT(


Get immediate free access to the Excel VLookup Sum Multiple Rows and Columns example workbook

Step 1 Example

I call the SUMPRODUCT function.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-rows-columns/
=SUMPRODUCT(
SUMPRODUCT function for VLookup sum multiple rows and columns


Get immediate free access to the Excel VLookup Sum Multiple Rows and Columns example workbook

Step 2: Set Up the Lookup Condition

Test whether the value (in each row inside the column you search in with the VLookup sum multiple rows and columns formula) matches the value you search for (the lookup value).

Do the following to carry out this conditional test inside a VLookup sum multiple rows and columns formula.

(1) Start with the SUMPRODUCT function (you called in step #1).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-rows-columns/
=SUMPRODUCT(

(2) Refer to the column (1 column, several rows) you search in (the column with the applicable data) for the lookup value.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-rows-columns/
=SUMPRODUCT(ColumnWhereYouSearchIn

(3) Use the equal to comparison operator to carry out the conditional test.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-rows-columns/
=SUMPRODUCT(ColumnWhereYouSearchIn=

(4) Specify the value you search for (the lookup value).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-rows-columns/
=SUMPRODUCT(ColumnWhereYouSearchIn=LookupValue

(5) Wrap the conditional test in parentheses.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-rows-columns/
=SUMPRODUCT((ColumnWhereYouSearchIn=LookupValue)

The conditional test you create in this step #2 returns:

  • An array of TRUEs and FALSEs.
  • One value (TRUE or FALSE) for each row (in the column where you search with the VLookup sum multiple rows and columns formula).
    • TRUE: The value in the applicable row matches the lookup value.
    • FALSE: The value in the applicable row doesn't match the lookup value.


Get immediate free access to the Excel VLookup Sum Multiple Rows and Columns example workbook

Step 2 Example

The column the VLookup sum multiple rows and columns example formula looks in is cells A7 to A26 ($A$7:$A$26, when using absolute references).

The lookup value for the VLookup sum multiple rows and columns example formula is stored in cell F7.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-rows-columns/
=SUMPRODUCT(($A$7:$A$26=F7)

The image below illustrates how this expression returns an array of TRUEs and FALSEs.

  • Column F (cells F7 to F26) return TRUE or FALSE, depending on whether the value in the applicable row (column A) matches (or not) the lookup value (stored in cell I7).
  • Cell G7 displays the formula (using the expression testing whether the value in the applicable row matches the lookup value) stored in column F.
Lookup condition for VLookup sum multiple rows and columns


Get immediate free access to the Excel VLookup Sum Multiple Rows and Columns example workbook

Step 3: Multiply by the Multiple Columns with the Values to Sum (Add)

Multiply the array of TRUEs and FALSEs (you created in step #2) by the multiple-column cell range with the values to sum (add).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-rows-columns/
=SUMPRODUCT((ColumnWhereYouSearchIn=LookupValue)*ColumnsWithValuesToAdd)

The multiplication operator (*):

  • As a general rule, multiplies 2 numbers.
  • When multiplying Boolean values (TRUE and FALSE), converts the Boolean values into 0s and 1s as follows:
    • TRUE is converted to 1.
    • FALSE is converted to 0.

When creating a VLookup sum multiple rows and columns formula:

  • The multiplication operator (*):
    • Multiplies 2 arrays:
      • An array of TRUEs and FALSEs; and
      • An array with the values in the columns to sum.
    • Returns an array with:
      • 0s, when the value in the applicable row doesn't match the lookup value; and
      • The values in the columns to sum, when the value in the applicable row matches the lookup value.
  • The SUMPRODUCT function returns the sum of the values in the array returned by the multiplication operator. These (summed or added) values are the values:
    • In the columns to sum;
    • For the rows where the value in the column you search in (and specified in step #2) matches the lookup value (you specified in step #2).


Get immediate free access to the Excel VLookup Sum Multiple Rows and Columns example workbook

Step 3 Example

The multiple columns with the values to sum (by the VLookup sum multiple rows and columns example formula) are in cells B7 to D26 ($B$7:$D$26, when using absolute references).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-rows-columns/
=SUMPRODUCT(($A$7:$A$26=F7)*$B$7:$D$26)

The image below illustrates how the expression with the multiplication operator (used as array1 argument of the SUMPRODUCT function) returns an array with:

  • 0s, when the value in the applicable row doesn't match the lookup value.
  • The values in the columns to sum, when the value in the applicable row matches the lookup value.

For these purposes:

  • Columns F, G, and H (cells F7 to H26) return:
    • 0, if the value in column A doesn't match the lookup value (stored in cell K7).
    • The value in one of the columns to sum (columns B, C, or D), if the value in column A matches the lookup value (stored in cell K7).
  • Cell I7 displays the formula (using the expression with the multiplication operator) stored in columns F, G, and H.
Columns to add with VLookup sum multiple rows and columns

The image below displays the result returned by the VLookup sum multiple rows and columns example formula.

VLookup sum multiple rows and columns formula example


Get immediate free access to the Excel VLookup Sum Multiple Rows and Columns example workbook

Download the VLookup Sum Multiple Rows and Columns Example Workbook

This Excel VLookup Sum Multiple Rows and Columns Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process above. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VLookup Sum Multiple Rows and Columns example workbook

This Excel VLookup Sum Multiple Rows and Columns Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

My Excel XLOOKUP Tutorial (click here to open) may help you:

  • Better understand and implement the contents above.
  • Better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

You can find more Excel Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials:

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link). The following are some of the topics covered in these Excel Courses:

  • Excel essentials and must-know skills.
  • Advanced Excel Formulas.
  • Excel Tables.
  • Pivot Tables.
  • Dashboards.
  • Power Pivot.
  • Power Query.

If you want to start learning how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).

guest
1 Comment
Most Voted
Newest Oldest
Inline Feedbacks
View all comments

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA. Here are some of my most popular Excel Training Resources:

  1. Free Excel VBA Email Course
  2. Excel Macro Tutorial for Beginners
  3. Excel Power Query (Get and Transform) Tutorial for Beginners
  4. Excel Keyboard Shortcut Cheat Sheet
  5. Excel Resources
About

Contact

Terms and Conditions

Privacy Policy

Limit of Liability and Disclaimer of Warranty

Affiliate Disclosure