Vlookup + sumif , but how????
 

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.

=)




--
xxxJessexxx


"Paul" wrote:


--
xxxJessexxx

Vlookup + sumif , but how????
 

how about

=SUMIF(D53:D65,a67,E53:E65)

which says, if any rows in D53 to D65 = A67, sum the corrsponding values in
E53:E65

"Jessejames" wrote:

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.

=)




--
xxxJessexxx


"Paul" wrote:


--
xxxJessexxx

Vlookup + sumif , but how????
 

Hi Jesse

Try
=SUMIF(DD,"qq",E:E)
or better still, place qq in a cell like A67 then use
=SUMIF(DD,"="&A67,E:E)
--
Regards
Roger Govier

Jessejames wrote:
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.

=)

Vlookup + sumif , but how????
 

Roger,

do you really need the equals in your second example? Plus, you missed
a colon between the DD in both examples:

=SUMIF(D:D,A67,E:E)

Hope this helps.

Pete

On Apr 21, 5:25*pm, Roger Govier
wrote:
Hi Jesse

Try
=SUMIF(DD,"qq",E:E)
or better still, place qq in a cell like A67 then use
=SUMIF(DD,"="&A67,E:E)
--
Regards
Roger Govier



Jessejames wrote:
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.

=)- Hide quoted text -

- Show quoted text -

Vlookup + sumif , but how????
 

Quite right, Pete!
Typing too quickly, and my mind was elsewhere!!!
--
Regards
Roger Govier

Pete_UK wrote:
Roger,

do you really need the equals in your second example? Plus, you missed
a colon between the DD in both examples:

=SUMIF(D:D,A67,E:E)

Hope this helps.

Pete

On Apr 21, 5:25 pm, Roger Govier
wrote:
Hi Jesse

Try
=SUMIF(DD,"qq",E:E)
or better still, place qq in a cell like A67 then use
=SUMIF(DD,"="&A67,E:E)
--
Regards
Roger Govier



Jessejames wrote:
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.
=)- Hide quoted text -
- Show quoted text -