[SOLVED] Advanced Lookup w/Dates

[Steve_C]

I am trying to do a forcast sheet for one customer. Here is the genral
layout of the sheet I am pulling data from.
P/N QTY Ship Date
a 5 2/7/06
a 2 3/5/06
a 8 3/5/06

What I need is the sum of Part "a" by ship date. Here is what I am using
now. This works but does not sum the quantitys with the same ship date.
INDEX(INDIRECT("'"&$S$9&"'!$Q$2:$Q$5"),MATCH($Q11&"_"&$R11,INDIRECT("'"&$S$9&"'!$F$2:$F$5")&"_"&INDIRECT("'"&$S$9&"'!$P$2:$P$5"),0))

Thanks
Steve_C

[daddylonglegs]

I can't work out your references but if you potentially have more than one quantity for a specific part number and ship date you probably need a different formula e.g.

=SUMPRODUCT(--(F2:F5=X1),--(G2:G5=Y1),H2:H5)

where F2:F5 contain your part numbers and X1 the specific part you wish to sum for and G2:G5 contains your dates and Y1 your specific date, H2:H5 contains your quantities

[Steve_C]

YOU ROCK!!
That works perfectly!

Thank you!
Steve

"daddylonglegs" wrote:

>
> I can't work out your references but if you potentially have more than
> one quantity for a specific part number and ship date you probably need
> a different formula e.g.
>
> =SUMPRODUCT(--(F2:F5=X1),--(G2:G5=Y1),H2:H5)
>
> where F2:F5 contain your part numbers and X1 the specific part you wish
> to sum for and G2:G5 contains your dates and Y1 your specific date,
> H2:H5 contains your quantities
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=509520
>
>