sumif with nested vlookup

**b.johnson** · 08-01-2007, 06:33 PM

Hi,

I have a spreadsheet that contains the full list all of our items in inventory.
For each job, I import a list of items and quanity of each item used.

On the inventory list I am using vlookup to match up the items in the import sheet and display the quanity.

=VLOOKUP($B2,IMPORT!$A$2:$J$200,8,FALSE)

So far so good but now i realize that I will sometimes have more than one entry on the import sheet that matches the criteria. Now I need the total to add all matching items.

I can use sumif on the import page but it would be alot cleaner if it could be accomplished in combination with the vlookup.

Thanks in advance for any ideas.

**davesexcel** · 08-01-2007, 07:44 PM

Here's a function from the Oz you can paste into a regular module

Function FindNth(Table As Range, Val1 As Variant, Val1Occrnce As Integer, _
ResultCol As Integer)

'''''''''''''''''''''''''''''''''''''''
'Written by OzGrid Business Applications
'www.ozgrid.com

'Finds the N'th value in the first Column of a table that has a stated _
value on the same row in another Column.
'''''''''''''''''''''''''''''''''''''''

Dim i As Integer
Dim iCount As Integer
Dim rCol As Range

For i = 1 To Table.Rows.Count
If Table.Cells(i, 1) = Val1 Then
iCount = iCount + 1
End If

If iCount = Val1Occrnce Then
FindNth = Table.Cells(i, ResultCol)
Exit For
End If
Next i
End Function

similar to the vlookup function your formula will be
=FindNth($A$4:$B$17,$C$4,1,2)
FindNth(range of data,value to lookup,1st instance,column)

So to find many results
=FindNth($A$4:$B$17,$C$4,1,2)
=FindNth($A$4:$B$17,$C$4,2,2)
=FindNth($A$4:$B$17,$C$4,3,2)
=FindNth($A$4:$B$17,$C$4,4,2)
=FindNth($A$4:$B$17,$C$4,5,2)

**b.johnson** · 08-02-2007, 11:07 AM

Thanks Dave.

Works great

The only thing of minor concearn (if I understand this correctly) is that a maximum number of matches has to be predetermined.

=FindNth(IMPORT!$A$2:$J$200,$B42,1,8)+FindNth(IMPORT!$A$2:$J$200,B42,2,8)+FindNth(IMPORT!$A$2:$J$200,B42,3,8)+FindNth(IMPORT!$A$2:$J$200,B42,4,8).........etc,etc

So, if I figure that the worst case scenario will have maybe 10 entries for the same item, I cover 20 instances to be safe. Now, on some hot august day in hell, there happens to be 21

Did I understand it correctly?

I was also looking at the sumproduct function but I had trouble wrapping my head around it.

**daddylonglegs** · 08-02-2007, 11:17 AM

Looks a little overcomplicated to me - just use SUMIF

=SUMIF(IMPORT!$A$2:$A$200,$B2,IMPORT!$H$2:$H$200)

**b.johnson** · 08-02-2007, 11:49 AM

Thanks for the input

That is what i had originally done but I had it separate on the import sheet.

I was looking for a way to incorporate it in my vlookup on the static inventory sheet because the import sheet gets overwritten.