Find Person & CountIf or Sum Between Dates

[mycon73]

Hi,

I have 2 Worksheets.

One is being used as my Task Log worksheet & has column O that may have 2 or more names in it, such as Supervisor 1 / Supervisor 2


On TATs worksheet, I'm looking for a function, such as a CountIf or Sumproduct that look for the person that I have in my Cell G48 & be able to find this individual between my specified dates in cells I48 & J48 or in this instance, 06/20 to 06/26.

The following 3 columns are not counting or adding up correctly.

# of Tasks Due This Week Column

Currently Using:

=IF(G48="","",SUMPRODUCT(--('Task Log'!$Q$13:$Q$570>=I48),--('Task Log'!$Q$13:$Q$570<=J48),--(ISNUMBER(SEARCH(G48,'Task Log'!$O$13:$O$570)))))

-----------------------

On-Time This Week Column

{=IF(G48="","",SUM((ISNUMBER((SEARCH("*"&G48&"*",'Task Log'!$O$13:$Q$570))))*('Task Log'!$Q$13:$Q$570>=I48)*('Task Log'!$Q$13:$Q$570<=J48)*ISNUMBER((SEARCH("*"&'Task Log'!$W$5&"*",'Task Log'!$W$13:$W$570)))))}

--------------------------------

Late This Week Column

{=IF(G48="","",SUM((ISNUMBER((SEARCH("*"&G48&"*",'Task Log'!$O$13:$Q$570))))*('Task Log'!$O$13:$O$570>=I48)*('Task Log'!$Q$13:$Q$570<=J48)*ISNUMBER((SEARCH("*"&'Task Log'!$W$7&"*",'Task Log'!$W$13:$W$570)))))}

Cells in Yellow on the TATs worksheet are the ones I need assistance with. If I can get one of these columns working, then that should be good enough. I should be able to apply the same function to my other 2 columns & just change its column references.

How can I modify these functions so it will count or sum properly? I attached an Excel 2007 file for example.

Thanks