VLookup return sum of multiple occurrences

Hello everyone,

I am having an issue with using VLookup to return the SUM of values that go with potentially multiple occurences of what I am searching for in a table. I have attached a sample document with roughly the same layout and situation that I am working with.

Basically, in the first worksheet (parts list), there are all the base part numbers that my group in responsible for (this list is potentially expanding). Then in the second worksheet (parts received), there is a list of all the parts and quantities that get sent to my company, some of which come up multiple times depending on the month/year they came in as well as have different modifications on the base part numbers. i.e. in the parts list worksheet I would have a part number 1234, while in the parts received worksheet I can have 1234 as well as 1234-01 that I would like to be accounted for.

I am trying to get a single number that represents the total number of parts received by my company that my group is responsible for. This number will be on the third worksheet (Sum or Parts Received). From looking into other threads I have tried a combination of a SUMIF as well as a VLookup as follows:

=SUMIF('Parts Received'!C15:C40,VLOOKUP('Parts List'!A2:A17,'Parts Received'!A15:F40,6,TRUE),'Parts Received'!F15:F40)

However this has been generating a value of 0 and I cannot seem to get it to change.

I appreciate any help that you guys have to offer!

P.S. Please note that the parts list on both the first and second worksheets are growing, and as I mentioned earlier there are cases where multiple entries with the same part numbers do occer in the second worksheet. Also, I am familiar with VBA so having a macro that performs this calculation is also a possibility.

Thanks!!

OK I modified the received part sheet and added in column g the following:

=IF(IFERROR(MATCH(C15,'Parts List'!A:A,0),0)>0,1,0) given that the part number is in column c and the parts numbers in the parts list are in column a.The iferror is to nullify the N/A response and place a 0 instead.If you are using a version prior to excel 2007 the iferror part wont work.
This flags those parts that are listed in the parts list sheet. Note that this has to be done for every entryin the parts received.

Then in the parts received sum sheet the sumif needs to be rewriten as follows:

=SUMIF('Parts Received'!G:G,1,'Parts Received'!F:F)

Originally Posted by rcm

OK I modified the received part sheet and added in column g the following:

=IF(IFERROR(MATCH(C15,'Parts List'!A:A,0),0)>0,1,0) given that the part number is in column c and the parts numbers in the parts list are in column a.The iferror is to nullify the N/A response and place a 0 instead.If you are using a version prior to excel 2007 the iferror part wont work.
This flags those parts that are listed in the parts list sheet. Note that this has to be done for every entryin the parts received.

Then in the parts received sum sheet the sumif needs to be rewriten as follows:

=SUMIF('Parts Received'!G:G,1,'Parts Received'!F:F)

Thank you for the quick response rcm! However, there is one problem with the MATCH function. If you look at the worksheet 'Parts Received' cell C16, the entry is 1234-05. In my master parts list on the first worksheet, there is in fact an entry with part number 1234. Since the bast part number is the same (the number before the dash), I require this to be marked as a match as well. Do you know of another way to match only the numbers before the dash?

Originally Posted by FDibbins

you could add a helper column and extract the part that you need, then base the search on that?

I tried the following however I still get an error on those dash values when I use the MATCH function. The helper column I made uses the following function to get the string before the dash:

=IFERROR((LEFT(C15,FIND("-",C15,1)-1)),C15)

Then I process to use what rcm gave me: =IF((IFERROR(MATCH(G16,'Parts List'!A:A,0),0)>0),1,0)
but I still get a value of 0 instead of 1 in the H column. In the column I of the excel sheet I just attached I sigled out the MATCH function and it gives me an error. Do you have any idea why it is doing this?

Thanks

Sample Doc VLookup.xlsx

Originally Posted by excelMTL

Thank you for the quick response rcm! However, there is one problem with the MATCH function. If you look at the worksheet 'Parts Received' cell C16, the entry is 1234-05. In my master parts list on the first worksheet, there is in fact an entry with part number 1234. Since the bast part number is the same (the number before the dash), I require this to be marked as a match as well. Do you know of another way to match only the numbers before the dash?

OK I went back to the original worksheet. The dash problem can be solved by adding another column containing a dashless part number in the received parts sheet. Then the basis of comparison column in the match function should reference that column. I also noticed that the code in parts list is not always "matching" although visually it is. I applied the trim() function to both code entries in their respective sheets to ensure a true match. so, in the part received sheet I added the following for each entry:

for row 15:

in column H: =IFERROR(FIND("-",C15),0) this is stripping the part code from the dash and the rest thereof.

in column i: =TRIM(IF(H15>0,LEFT(C15,H15-1),C15)) if the code contains a dash just the left part of the code is taken, if not the whole code is recorded.

The match function in column G had to be =IF(IFERROR(MATCH(I15,'Parts List'!C:C,0),0)>0,1,0) since it needed to compare column I intead


Notice that the match function references parts list column c. That is because the part code written in column a has blanks embedded, so column c contains the trimmed version of column a.

This works and renders 174 parts.

I'll work a macro to avoid adding all this columns to your entries and send it asap.

Originally Posted by rcm

I'll work a macro to avoid adding all this columns to your entries and send it asap.

Thank you both for the excellent options! rcm if you could complete a macro to perform all these functions that would be fantastic!

Thanks again.

try this

I took the liberty to sum up by part number, assumming that only one person in resposible for each part number.

again, it all adds to 174

Sub partsreceived()
Dim i, j, pr, partliststart, receivedpartsstart As Integer
Dim code As String

fn1 = "parts list"
fn2 = "parts Received"
fn3 = "Sum of Parts Received"

parts = 0

receivedpartsstart = 15
partliststart = 2

Worksheets(fn2).Select

i = receivedpartsstart
While Cells(i, 1) <> ""
code = Trim(Cells(i, 3))
j = 1
While j <= Len(code) And Mid(code, j, 1) <> "-"
j = j + 1
Wend

code = Left(code, j - 1)
pr = Cells(i, 6)

Worksheets(fn1).Select

'This can be done by a Vlookup() but I can get it to work

j = partliststart
While code <> Trim(Cells(j, 1)) And Cells(j, 1) <> ""
j = j + 1
Wend
If Trim(Cells(j, 1)) = code Then
parts = parts + pr
Cells(j, 3) = Cells(j, 3) + pr
End If
i = i + 1
Worksheets(fn2).Select
Wend

Worksheets(fn3).Cells(3, 7) = parts

End Sub

Thanks so much!