LookUp with multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Back
Top