LookUp with multiple criteria

[Guest]

My data is in worksheet1 as follows (the headers start in cell A1), with 3
rows for each date of the month:

date code num
2/1/06 B 100
2/1/06 C 200
2/1/06 W 300
2/2/06 B 400
2/2/06 C 500
2/2/06 W 600

On worksheet2 I want to enter a date in cell A1 and then in cell B1 return
the value in the "num" column based on the code(B,C,W) for that date

Thanks for your help

 

[Guest]

Try something like this:

With the scenario you posted....

On Sheet2
A1: 2/2/2006
A2: B
B1:
=SUMPRODUCT((Sheet1!A2:A7=Sheet2!A1)*(Sheet1!B2:B7=Sheet2!A2)*Sheet1!C2:C7)
In this instance, that formula returns 400.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

This was exactly what I was looking for. Thanks very much