Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to vlookup and sum matches in rows or columns in Excel?

Using vlookup and sum function helps you quickly find out the specified criteria and sum the corresponding values at the same time. In this article, we are going to show you two methods to vlookup and sum the first or all matched values in rows or columns in Excel.

Vlookup and sum matches in a row or multiple rows with formulas
Vlookup and sum matches in a column with formulas
Easily vlookup and sum matches in rows or columns with an amazing tool

More tutorials for VLOOKUP...


Easily vlookup and sum matches in rows or columns in Excel:

The LOOKUP and Sum utility of Kutools for Excel can help you quickly vlookup and sum matches in rows or columns in Excel as the below demo shown.
Download the full feature 30-day free trail of Kutools for Excel now!


Vlookup and sum matches in a row or multiple rows with formulas

The formulas in this section can help to sum the first or all matched values in a row or multiple rows based on specific criteria in Excel. Please do as follows.

Vlookup and sum the first matched value in a row

Supposing you have a fruits table as the below screenshot shown, and you need to lookup the first Apple in the table and then sum all corresponding values in the same row. To achieve this, please do as follows.

1. Select a blank cell to output the result, here I select cell B10. Copy the below formula into it and press the Ctrl + Shift + Enter keys to get the result.

=SUM(VLOOKUP(A10, $A$2:$F$7, {2,3,4,5,6}, FALSE))

Notes:

  • A10 is the cell containing the value you are looking for;
  • $A$2:$F$7 is the data table range (without headers) which include the lookup value and the matched values;
  • The number {2,3,4,5,6} represents that the result value columns start with the second column and end up with the sixth column of the table. If the number of result columns are more than 6, please change {2,3,4,5,6} to {2,3,4,5,6,7,8,9….}.
Vlookup and sum all matched values in multiple rows

The above formula can only sum values in a row for the first matched value. If you want to return the sum of all matches in multiple rows, please do as follows.

1. Select a blank cell (in this case I select cell B10), copy the below formula into it and press the Enter key to get the result.

=SUMPRODUCT((A2:A7=A10)*B2:F7)


Vlookup and sum matched value in a column with formulas

This section provides a formula to return the sum of a column in Excel based on specific criteria. As the below screenshot shown, you are looking for the column title “Jan” in the fruit table, and then sum the whole column values. Please do as follows.

1. Select a blank cell, copy the below formula into it and press the Enter key to get the result.

=SUM(INDEX(B2:F7,0,MATCH(A10,B1:F1,0)))


Easily vlookup and sum matches in rows or columns with an amazing tool

If you are not good at applying formula, here recommend you the Vlookup and Sum feature of Kutools for Excel. With this feature, you can easily vlookup and sum matches in rows or columns with only clicks.

Before applying Kutools for Excel, please download and install it firstly.

Vlookup and sum the first or all matched values in a row or multiple rows

1. Click Kutools > Super LOOKUP > LOOKUP and Sum to enable the feature. See screenshot:

2. In the LOOKUP and Sum dialog box, please configure as follows.

  • 2.1) In the Lookup and Sum Type section, select the Lookup and sum matched value(s) in row(s) option;
  • 2.2) In the Lookup Values box, select the cell which contains the value you are looking for;
  • 2.3) In the Output Range box, select a cell to output the result;
  • 2.4) In the Data table range box, select the table range without the column headers;
  • 2.5) In the Options section, if you want to sum values only for the first matched one, choose the Return the sum of the first matched value option. If you want to sum values for all matches, select the Return the sum of all match values option;
  • 2.6) Click the OK button to get the result immediately. See screenshot:

Note: If you want to vlookup and sum the first or all matched values in a column or multiple columns, please check the Lookup and sum matched value(s) in column(s) option in the dialog box, and then configure as the below screenshot shown.

For more details of this feature, please click here.

  If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.


related articles

Vlookup values across multiple worksheets
You can apply the vlookup function to return the matching values in a table of a worksheet. However, if you need to vlookup value across multiple worksheets, how can you do? This article provides detailed steps to help you easily solve the problem.

Vlookup and return matched values in multiple columns
Normally, applying the Vlookup function can only return the matched value from one column. Sometimes, you may need to extract matched values from multiple columns based on the criteria. Here is the solution for you.

Vlookup to return multiple values in one cell
Normally, when applying the VLOOKUP function, if there are multiple values that match the criteria, you can only get the result of the first one. If you want to return all matched results and display them all in a single cell, how can you achieve?

Vlookup and return entire row of a matched value
Normally, using the vlookup function can only return a result from a certain column in the same row. This article is going to show you how to return the whole row of data based on specific criteria.

Backwards Vlookup or in reverse order
In general, the VLOOKUP function searches values from left to right in the array table, and it requires the lookup value must stay in the left side of target value. But, sometimes you may know the target value and want to find out the lookup value in reverse. Therefore, you need to vlookup backwards in Excel. There are several ways in this article to deal with this problem easily!

More tutorials for VLOOKUP...


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Eric · 4 months ago
    the =SUM(PRODUCT((A2:A7=A11)*B2:I7) is not working with decimal point.
    • To post as a guest, your comment is unpublished.
      Magda · 3 months ago
      For me works just fine. Try without separating SUM and Product, it should be =SUMPRODUCT((A2:A7=A11)*B2:I7)
  • To post as a guest, your comment is unpublished.
    Jelly · 5 months ago
    can one sum things up in a column and not a row?
  • To post as a guest, your comment is unpublished.
    Kevin H · 7 months ago
    There's a typo above in the "VLOOKUP AND SUM ALL MATCHES IN COLUMNS IN EXCEL" section:

    =SUM(PRODUCT((A2:A7=A11)*B2:I7) is incorrect. Should be:

    =SUMPRODUCT((A2:A7=A11)*B2:I7)
    • To post as a guest, your comment is unpublished.
      Laura · 2 months ago
      So glad I came to read the comments, was struggling to figure out why it wasn't working! Thanks Kevin!
    • To post as a guest, your comment is unpublished.
      me · 3 months ago
      god bless you :))
  • To post as a guest, your comment is unpublished.
    D Jaikumar · 7 months ago
    I have some BOQ & dispatched part qty on different dates. How can match with BOQ using sum & vlookup ?
  • To post as a guest, your comment is unpublished.
    Jay · 10 months ago
    Hi, The sum with vlookup is not functioning to my excel. Still showing the single value. For example suppose sum up values from columns 2,3,4,5,..I am getting a value from the column 2 only. Could you please explain on the errors?
    • To post as a guest, your comment is unpublished.
      crystal · 9 months ago
      Hi Jay,
      Don't forget to press the Shift + Ctrl + Enter keys while still in edit mode after entering the formula.
  • To post as a guest, your comment is unpublished.
    IVon · 11 months ago
    Say Something Here.....