Skip to main content

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...

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)

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 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...

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description

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!
Comments (26)
No ratings yet. Be the first to rate!
Marnel Strydom
Marnel Strydom
This comment was minimized by the moderator on the site
I need help with a formula. I have 2 sheets to pull data from and calculate the sum of on column with a match to a name.
I.e Name is in sheet 1 D5, the name in sheet 2 is B3 the amounts to calculate which match the name in B3 in sheet 2 is F3 to F32.
I've tried sum index, vlookup, match and so on but keep getting a 0. Can anyone assist with this?
Reply
0
0
crystal
crystal     Marnel Strydom
This comment was minimized by the moderator on the site
Hi Marnel Strydom,
I don't know if I understand you correctly: If the name in D5 of Sheet1 matches the name in B3 of Sheet2, then sum numbers in the range F3:F32 of Sheet2.
If so, you can apply the following formula to get it done.
=SUM(INDEX(Sheet2!F3:F32,0,MATCH(Sheet1!D5,Sheet2!B3,0)))
Reply
Report
0
0
Joe
Joe
This comment was minimized by the moderator on the site
I have a multi sheet spread sheet keeping track of job hours. I have used VLOOKUP in succession to sum all the hours on multiple sheets and it works great... Until it gets to a sheet that does not contain the lookup value. I have searched all over for my issue, and VLOOKUP may be the incorrect solution. I was wondering if I could rattle anyone's brain to make this work.

I.E. I have 1 excel document with 52 tabs. Each tab is a work week starting from January so WW1 is all the hours FOR sed jobs I did for that week. "joes house 2 hours ; mikes house 3 hours"... WW2, WW3 etc... Until WW52.

This is the function I made to add hours together...

=SUM(VLOOKUP(O30,'WW29'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW30'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW31'!$A$7:$M$110,{13},FALSE)) And it works great. But when that job is finished it is not on (for example WW32 tab). Hence I get the #N/A error. so for example, as the previous one works great when I expand the formula to cover all 52 sheets... (EXAMPLE OF NEXT PAGE WIOTHOUT LOOKUP VALUE)

=SUM(VLOOKUP(O30,'WW29'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW30'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW31'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW32'!$A$7:$M$110,{13},FALSE)) I get the #N/A error because the job is not listed on WW32. But I may add hours to that on WW45.

Is there a way to make VLOOKUP skip a sheet that does not have the referenced value and continue summing it till the end? I apologize, this may be as clear as mud but I will clarify anything if need be.

I have also tried IFERROR. You can set IFERROR to return text or even blanks, but does not seem to cover summing. I'm looking for how to SUM multiple sheets when some of the sheets do not contain the lookup value. When using IFERROR function, instead of RETURNING #N/A it just returns "YOU'VE ENTERERED TOO MANY ARGUMENTS FOR THIS FUNCTION"...

=IFERROR(VLOOKUP(O30,'WW29'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW30'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW31'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW32'!$A$7:$M$110,{13},FALSE),"")

And that's just 3 sheets.

Any help would be greatly appreciated.

P.S. I have tried with CTRL+SHIFT+ENTER as well to no avail.
Reply
0
0
crystal
crystal     Joe
This comment was minimized by the moderator on the site
Hi Joe,
The methods provided in the following article can do you a favor. Please give it a try. Hope I can help.
How To Vlookup Across Multiple Sheets And Sum Results In Excel?
Reply
Report
0
0
Geoff
Geoff
This comment was minimized by the moderator on the site
A B C D E F
1 I want this cells in col B to sum the values in col F7 (today) to say F20. this will reduce as tomorrow it will sum the values from F8:F20 and so on -$19 I
want this cells in column B to sum the values in col F7 (today) to say F10.
this will reduce as tomorrow it will sum the values from F8:F10 and so on

2 Fri 22 Jul 22 -$19
3 Sat 23 Jul 22 -$19
4 Sun 24 Jul 22 -$19
5 Mon 25 Jul 22 -$19
6 Tue 26 Jul 22 -$19
7 Wed 27 Jul 22 -$19 tried with vlookup, needless to say it doesn't work
8 Thu 28 Jul 22 -$19 =L8=(D1-E1)+SUM(vlookup(today(),6,false):F28)
9 Fri 29 Jul 22 -$19
10 Sat 30 Jul 22 -$19
11 Sun 31 Jul 22 -$19
12 Mon 01 Aug 22 -$19
13 Tue 02 Aug 22 -$19
14 Wed 03 Aug 22 -$19
15 Thu 04 Aug 22 -$19
Reply
0
0
Alqattan
Alqattan
This comment was minimized by the moderator on the site
hi
How can I add multiple vlookups together and sum it up ?


=VLOOKUP(E3,'Waste Process NEW'!N:O,2,FALSE) --------------- for this everything goes fine except that retune valve is only the first one where N column have many values match's lookup values of E3.


using index didn't help and shows #N/A
=SUM(INDEX('Waste Process NEW'!N:N,0,MATCH('Monthly Report'!H5,'Waste Process NEW'!1:1048576,0)))
Reply
Report
0
0
crystal
crystal     Alqattan
This comment was minimized by the moderator on the site
Hi tariq,
Would you mind providing a screenshot of your data? Sorry for the inconvenience.
Reply
Report
0
0
Drew
Drew
This comment was minimized by the moderator on the site
I just get a #VALUE! when I try to do all matched values, but it works for the first value. Any idea?
Reply
0
0
Mayce Lee
Mayce Lee
This comment was minimized by the moderator on the site
How can I add multiple vlookups together, for example I want to look up from your example Apples + oranges + bananas for January.Is there a way to do this?
Reply
0
0
Mike
Mike
This comment was minimized by the moderator on the site
I'm having trouble with a similar formula I feel like I need to use an index but can't figure it out. I'm trying to get the sum but the lookup is based on 2 values. Column D which contains an employee's extension and column I which provides a logout code. The goal is to find the sum of time in column K based on a specific employee (column D) and the logout code (column I).
Reply
0
0
crystal
crystal     Mike
This comment was minimized by the moderator on the site
Hi Mike,
Do you mind uploading a screenshot of your data?
Reply
Report
0
0
Mike
Mike     crystal
This comment was minimized by the moderator on the site
Thanks but I was able to get my formula up and running with a sumifs calculation. Sumifs giving the sum of something based on multiple factors. Mine being an employee ID number and a status code.
Reply
0
0
Pinak
Pinak
This comment was minimized by the moderator on the site
what will be the formula to add qty of apple in jan only
Reply
0
0
Eric
Eric
This comment was minimized by the moderator on the site
the =SUM(PRODUCT((A2:A7=A11)*B2:I7) is not working with decimal point.
Reply
0
0
Magda
Magda     Eric
This comment was minimized by the moderator on the site
For me works just fine. Try without separating SUM and Product, it should be =SUMPRODUCT((A2:A7=A11)*B2:I7)
Reply
0
0
Jelly
Jelly
This comment was minimized by the moderator on the site
can one sum things up in a column and not a row?
Reply
0
0
crystal
crystal     Jelly
This comment was minimized by the moderator on the site
Hi Jelly,
This formula =SUM(INDEX(B2:F9,0,MATCH(A12,B1:F1,0))) can help you solve the problem. Please have a try. Hope I can help.
View Attachments (1 / 5)
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations