How to lookup multiple values and summing them up in one cell

[Amr Abul Laban]

I'd like to combine a sumif and vlookup function. Basically I want to lookup
a "Name" in a table and sum up the corresponding grades (multiple cells) in
another table. Can anyone help. Thanks

 

[Duke Carey]

Excel can most probably do what you want, however, to get the best advice
from the people here you probably ought to give us abetter idea of how your
data is structured and just exactly what you are trying/wanting to do

 

[CLR]

You can use a regular Vlookup formula, and instead of returning a value, you
can return a formula (or actually the result thereof).........the formula
can be in the second column of your table and actually only the result of
the formula will show there.......

A1= a
B1= =VLOOKUP(A1,G1:H5,2,FALSE)
G1=a, G2=b, G3=c, G4=d, G5=e
H1= =SUM(I1:K1), and copy down to H5

Then, B1 will return the sum of I1:K1........

hth
Vaya con Dios,
Chuck, CABGx3

 

[Amr Abul Laban]

Thanks for the quick reply. I just figured it out. Unbelievable how it took
me so long to come up with a simple formula. But thanks again, I didn't
imagine that someone would reply this quickly. I will have to use this
resource more often. Thank you very much and greetings from Frankfurt am
Main. Cheers

 

[John K]

Could you share how you did it?

John K

Thanks for the quick reply. I just figured it out. Unbelievable how it took
me so long to come up with a simple formula. But thanks again, I didn't
imagine that someone would reply this quickly. I will have to use this
resource more often. Thank you very much and greetings from Frankfurt am
Main. Cheers

 

[Ari]

What was the exact formular you used to figure this out? That is combining
sumif with a vlookup formular?

Thanks.

 

[Ashish Mathur]

Hi,

You may refer to the following

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Jessejames]

reply to CLR

thanks for that info. but one condition i will add,
what if that cell a1 has 2 or more same. their value should be add up.
i hope you get my clarification.
thanks in advance.

 

[Paul]

Not sure I'm following correctly, but if you want to sum values in
column B based on matching entries in column A, a standard SUMIF will
work. If you have more than one criteria you can use SUMPRODUCT (or in
Excel 2007 use SUMIFS).

=SUMIF(A1:A20,"something to match",B1:B20)

=SUMPRODUCT(--(A1:A20="something"),--(C1:C20="something else"),B1:B20)

=SUMIFS(B1:B20,A1:A20,"something",C1:C20,"something else")

 

[Jessejames]

thanks paul for the reply.
my clarification is. the combination of vlookup plus the sumif function. are
they possible to formulate in one full command/program?

based on CLR sample, he is already right and the one i ask if there is same
product name or name in searching and then it will automatically add up.
thanks =)

 

[Jessejames]

this is my database

(ROW and column A52 IS description)

A B C D E
F G
description data b data c name tot tot a totb

purple 1 1 qq 12000 0 12000.0
yellow 1 1 ww 630 0 630.0
blue 1 1 ee 1341 0 1341.0
orange 1 1 rr 17548 0 17548.0
black 1 1 tt 228 0 228.0
purple 1 1 yy 131 0 131.0
black 1 1 uu 27090 0 27090.0
blue 1 1 gg 1854 0 1854.0
black 1 1 ff 3975 0 3975.0
black 1 1 dd 53620 0 53620.0
blue 1 1 ss 87226 0 87226.0
purple 1 1 qq 16000 0 16000.0


query 1 = to look the value ee which is located at column d

ee =VLOOKUP(A67,D53:E65,2,FALSE)

correct

query 2 = to look the value of qq which is located also at column D but they
have 2 quantities, so they have to add.
answer = ???

thanks for helping.

=)

 

[Pete_UK]

Check out your other post.

Pete

this is my database

(ROW and column A52 IS description)

         A                 B           C          D            E            
 F              G
 description   data b    data c   name  tot       tot a       totb  

purple        1    1       qq   12000      0    12000.0
yellow        1            1       ww   630        0    630.0
blue          1    1       ee   1341       0    1341.0
orange       1     1       rr   17548               0   17548.0
black        1     1       tt   228         0  228.0
purple      1      1       yy   131         0  131.0
black      1       1      uu    27090      0    27090.0
blue       1       1      gg    1854       0    1854.0
black      1       1      ff    3975       0    3975.0
black      1       1      dd    53620      0    53620.0
blue       1       1      ss    87226      0    87226.0
purple     1       1      qq    16000      0    16000.0

query 1 = to look the value ee which is located at column d

ee  =VLOOKUP(A67,D53:E65,2,FALSE)

correct

query 2 = to look the value of qq which is located also at column D butthey
have 2 quantities, so they have to add.
answer = ???

thanks for helping.

=)

--
xxxJessexxx










Jessejames;702836 Wrote:






- Show quoted text -- Hide quoted text -

- Show quoted text -