i guess I am cunfused as to the range one would =C9
and range 2 would = D9 same for the third.
If my data wher typed in A1 B1 and the formula to
produce in C1

"Ashish Mathur" wrote:

Hi,

Try this. Please adjust the range as desired.

SUMPRODUCT((C6:C7=C9)*(D6:D7<=D9)*(E6:E7=D9),F6:F 7)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

" wrote in message
...


OK I am still trying to figure out an old problem

OK if make the sheet simple
A1 A2 A3
EMP # Date EMP Profile code

TAble array is set up like
L1 M1 N1 O1
Agent # Start Date End Date EMP profile code
177721 1-1-2009 5-31-2009 1234
177721 6-1-2009 12-31-2009 5678

Ok so the big issue is the emp will chage profile peroidically
thoughout a calander year.
I am trying to get were in A1 is you enter the EMP # and
then in B1 you enter a random date the correct profile code
will come up in C1 based on the date range in the of table array
the emp was profiled to the code

=SUMPRODUCT(--(A1=L1:L9),--(B1=M1:M9),--(B1<=N1:N9),O1:O9)
if not woeking me only, it sticks a 0 regardless of the emp and date
entered.

completely open to re-structering the array is needed. As matter of fact
the
table array is a work in progress