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 find individual in Cell G48, regardless if the individual is assigned to a single task or multiple tasks & count or sum properly between my specified date ranges?
I would upload an example file, but I don't see where I can do this.
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 find individual in Cell G48, regardless if the individual is assigned to a single task or multiple tasks & count or sum properly between my specified date ranges?
I would upload an example file, but I don't see where I can do this.
