summing multiples lines in a vlookup function

[Rob T.]

I have a vlookup function within a spreadsheet that only pulls half of what I
need. The vlookup is pulling the first correct value based on the find
criteria instead of all items, so I need to create a function that will pull
all the exact matches and sum them together. Any help would be appreciated.

Thanks
Rob

 

[~L]

Sounds like you need Sumproduct.

Examples:
=Sumproduct(--($A$1:$A$50="Type A"),--($B$1:$B$50="Catfish"),$D$1:$D$50)

Or replace text with cell references:

=Sumproduct(--($A$1:$A$50=F1),--($B$1:$B$50=G1),$D$1:$D$50)

 

[Rob T.]

how do i incorporate that into a vlookup function

 

[~L]

You would use Sumproduct rather than VLookup.

Where Vlookup's Syntax is =VLOOKUP(Lookup Value, Lookup Rable, Column
Number, Range Lookup T/F)

Sumproduct's could be considered as:

=SUMPRODUCT(--(Lookup Range1=Lookup Value1),--(Lookup Range2=Lookup
Value2),--(Lookup RangeN = Lookup ValueN),Sum Range)

Having trouble finding the explanation I really like of the details of how
this works, but

http://www.exceluser.com/explore/sumproduct_12.htm

is a decent resource.

 

[Glenn]

You don't. In this case, SUMPRODUCT() replaces VLOOKOUP(). If you need more
help, give specifics of how your worksheet is setup.

Or, take a look at this:

http://www.contextures.com/xlFunctions01.html#SumProduct

 

[Ashish Mathur]

Hi,

You can use the formula at the following link to extract all the values
against a name. Once you have done that, you can use the SUM() function.

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com