[hong]

Hello!

I have a set of data, Part# is 1001, 1002,....
Destionation is different
A(Part#) B(Destination) Date1 Date2 Date3...............
1001 Canada 100 105 110.....
1001 USA 50 60 70

Because the same part# have different destination, and the the same
destination has different part#. (This is a big excel sheet, and I get it
everyday with different Qty in different order)

I have another excel sheet to get the number in the same format as above,
but I am trying to use vlook up to look up two condition, for example, look
up part#: A and also look up Destination: Canada , so I can get the QTY for
date1 and so on.
I don't need to lookup the date, because the date are always in sequence.

Please help me on the formula!
Thank you!

[ShaneDevenshire]

Hi,

You can use something like this

=SUMPRODUCT(--(1001=$A$1:$A$1100),--("Canada"=$B$1:$B$1100),C1:C1100)

you can change the 1001 and Canada to cell references if you want. You
could modify it to handle a variable date.


If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Hong" wrote:

Hello!

I have a set of data, Part# is 1001, 1002,....
Destionation is different
A(Part#) B(Destination) Date1 Date2 Date3...............
1001 Canada 100 105 110.....
1001 USA 50 60 70

Because the same part# have different destination, and the the same
destination has different part#. (This is a big excel sheet, and I get it
everyday with different Qty in different order)

I have another excel sheet to get the number in the same format as above,
but I am trying to use vlook up to look up two condition, for example, look
up part#: A and also look up Destination: Canada , so I can get the QTY for
date1 and so on.
I don't need to lookup the date, because the date are always in sequence.

Please help me on the formula!
Thank you!

[hong]

Thank you so much, it works great!
But would you explain to me why need to use "--",
it doesn't work without "--"

Thank you!

"ShaneDevenshire" wrote:

Hi,

You can use something like this

=SUMPRODUCT(--(1001=$A$1:$A$1100),--("Canada"=$B$1:$B$1100),C1:C1100)

you can change the 1001 and Canada to cell references if you want. You
could modify it to handle a variable date.


If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Hong" wrote:

Hello!

I have a set of data, Part# is 1001, 1002,....
Destionation is different
A(Part#) B(Destination) Date1 Date2 Date3...............
1001 Canada 100 105 110.....
1001 USA 50 60 70

Because the same part# have different destination, and the the same
destination has different part#. (This is a big excel sheet, and I get it
everyday with different Qty in different order)

I have another excel sheet to get the number in the same format as above,
but I am trying to use vlook up to look up two condition, for example, look
up part#: A and also look up Destination: Canada , so I can get the QTY for
date1 and so on.
I don't need to lookup the date, because the date are always in sequence.

Please help me on the formula!
Thank you!

[David Biddulph[_2_]]

http://www.mcgimpsey.com/excel/formulae/doubleneg.html
http://xldynamic.com/source/xld.SUMPRODUCT.html
--
David Biddulph

"Hong" wrote in message
...
Thank you so much, it works great!
But would you explain to me why need to use "--",
it doesn't work without "--"

Thank you!

"ShaneDevenshire" wrote:

Hi,

You can use something like this

=SUMPRODUCT(--(1001=$A$1:$A$1100),--("Canada"=$B$1:$B$1100),C1:C1100)

you can change the 1001 and Canada to cell references if you want. You
could modify it to handle a variable date.


If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Hong" wrote:

Hello!

I have a set of data, Part# is 1001, 1002,....
Destionation is different
A(Part#) B(Destination) Date1 Date2 Date3...............
1001 Canada 100 105 110.....
1001 USA 50 60 70

Because the same part# have different destination, and the the same
destination has different part#. (This is a big excel sheet, and I get
it
everyday with different Qty in different order)

I have another excel sheet to get the number in the same format as
above,
but I am trying to use vlook up to look up two condition, for example,
look
up part#: A and also look up Destination: Canada , so I can get the QTY
for
date1 and so on.
I don't need to lookup the date, because the date are always in
sequence.

Please help me on the formula!
Thank you!

[hong]

Thank you so much.

Is that possible I use VB to get the data?
For example, create a command button with programming to get the data,
rather than set all the cells with formula?

I am trying to learn the VB in excel.

Thank you!



"David Biddulph" wrote:

http://www.mcgimpsey.com/excel/formulae/doubleneg.html
http://xldynamic.com/source/xld.SUMPRODUCT.html
--
David Biddulph

"Hong" wrote in message
...
Thank you so much, it works great!
But would you explain to me why need to use "--",
it doesn't work without "--"

Thank you!

"ShaneDevenshire" wrote:

Hi,

You can use something like this

=SUMPRODUCT(--(1001=$A$1:$A$1100),--("Canada"=$B$1:$B$1100),C1:C1100)

you can change the 1001 and Canada to cell references if you want. You
could modify it to handle a variable date.


If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Hong" wrote:

Hello!

I have a set of data, Part# is 1001, 1002,....
Destionation is different
A(Part#) B(Destination) Date1 Date2 Date3...............
1001 Canada 100 105 110.....
1001 USA 50 60 70

Because the same part# have different destination, and the the same
destination has different part#. (This is a big excel sheet, and I get
it
everyday with different Qty in different order)

I have another excel sheet to get the number in the same format as
above,
but I am trying to use vlook up to look up two condition, for example,
look
up part#: A and also look up Destination: Canada , so I can get the QTY
for
date1 and so on.
I don't need to lookup the date, because the date are always in
sequence.

Please help me on the formula!
Thank you!