VLOOKUP for multiple results

[Steamboat]

Hi,

Right now I'm using VLOOKUP to return a value, however I need VLOOKUP to return the sum of the values it's looking up IF if finds the value more than once. Example:

=IF(COUNT(VLOOKUP(A1927,range,6,FALSE))>0,VLOOKUP(A1927,range,6,FALSE),0)

this is the formula I use right now. It works, except when the value of cell A1927 appears twice in the "range", it does not return the sum of each instance of the sixth column for each time the value of cell A1927 appears in the "range."

Is there a function (or formula) I can use to sum the values for every time VLOOKUP is true?

Thanks ahead of time,

Rob

[Ron Coderre]

For demo purposes
my target value is in Cell A1,
my lookup column is C and
my value-to-be-added column is D:

B1: =SUMPRODUCT(--(C1:C16=$A$1),D1:D16)

I this this format will work for you if you want the sum of the values.

Does that help?
Ron

[Steamboat]

Let me try to clarify with an example.

this info is in a tab called "Tab1"
A B C
1 12 #
2 11 #
3 10 #

below is the info in "Tab2"
A B C
1 10 #
1 15 #
2 12 #

Here is my formula (which is in Tab1)
=IF(COUNT(VLOOKUP(A1,Tab2,2,FALSE)))

Right now this formula returns "10." But see how the 1 appears twice in column A of Tab2? I want to write a formula that will return "25" because it is adding every value in column B that matches VLOOKUP for A1 in Tab1. But at the same time, if I did a VLOOKUP for A2 from Tab1, it returns 12. Does that help clarify?

Thanks again,

Rob

[Ron Coderre]

Hi, Rob

First: You need the type of formula entered below.
=SUMPRODUCT(--(A1:A10=$A$1),B1:B10)

Second: It works by checking the values in one column and adding corresponding values in another column. Those 2 columns need to be listed individually. Consequently, you can't reference a multi-column range (TAB2 or A1:B10).

Third: If the Tab2 range is on a different sheet, then you'll need to reference that sheet in the formula:
=SUMPRODUCT(--(Sheet2!A1:A10=$A$1),Sheet2!B1:B10)

OR

You could create 2 named ranges on Sheet2 and reference them:
=SUMPRODUCT(--(Tab2FirstCol=$A$1),Tab2SecondCol)

Am I helping, yet?

Ron

[Steamboat]

I'm sure that you are helping, it's just that I'm not the greatest with Excel. I really do appreciate your help though. Right now I'm at home and the spreadsheet is at work. I'll try to wrap my brain around your suggestions and get back with you. Thanks again!

[Ron Coderre]

Ya, know....after looking at your example, the SUMIF function would be the better approach. You'd still need to reference 2 ranges, though. Put this function somewhere on Sheet1 (or whatever sheet Tab1 is on).
=SUMIF(Sheet2!$A$1:$A$10,A1,Sheet2!$B$1:$B$10)

That function is the more elegant way to calculate what you want.
If the Tab2 range is not on Sheet2, replace the Sheet2 reference with the correct sheet name.


Regards,
Ron