VLOOKUP for multiple results

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

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

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

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

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!

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

=SUMIF(RPC!$A$1:$A$1927,A1,epay!$F$1:$F$1194)

This is exactly the formula I tried. It returns 0, but it should be returning $129.30.
It resides on the RPC tab. Any idea what I'm doing wrong?

Thank you very much for all your help! I modified your formula to work, here it is:

=IF(VLOOKUP(A2,epay,1)=A2,SUMIF(epay!A:A,A2,epay!F:F),0)

Thanks again!

Rob

I'm glad you figured it out.

One question, two comments:

1)By skipping the 4th argument of the VLookup, it defaults to an approximate match....Is that OK?
IF NO, then you need:
=IF(VLOOKUP(A2,epay,1,0)=A2,SUMIF(epay!A:A,A2,epay!F :F),0)

2)Do you have a Sheet AND a Range each named epay? I usually try to avoid that situation.

3)I'm pretty sure you could cull your formula down to:
=SUMIF(epay!A:A,A2,epay!F:F)

Regards,
Ron