Compare two dates with two other dates in a whole column.

So I have two tables, task table and user-state table. As you can see in the image.
Task table has the start and end date for each task completed by an employee.
And the user-state table has the start and end date for various activities performed by each employee.
Now I want to have another column in task table called 'Available Time'. Wherein I want to calculate if for the time in between the start and end date of the task, there was a corresponding value in the user-state table with 'User_State' as 'Available' for the same employee name, then the value of the column 'Duration in Sec' is shown in the 'Available Time' column. And if there is more than one record with 'Available' state, then they should be summed up.

• The condition for dates is as follows:
Task_Start_Date <= State_Start_Date
AND
Task_End_Date >= State_Start_Date

• The other condition is that emp name should be same in both tables and User_State should be 'Available'.

Attaching the excel file for reference.

I tried sumif but it looks one cell to cell. I want to look in the whole column. I think array function might work.
Any help would be appreciated.

Try in D2 and copy down

Formula:

Please Login or Register  to view this content.