## Sunday, June 23, 2013

### yogi_ Determine Availability Of Staff By Name From Data In Project Management Info Table

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 23, 2013
Multiple Condition Issue
Hi,
This is my problem statement -
To check if a person is free between a given set of date range.
I tried searching in the group, and I incorporated some of the suggestions based which I got the solution for Excel, but not for Google Spreadsheet.
I have a task sheet with multiple set of tasks assigned to various people. Each row as the task name/details, person responsible, plan/actual start and end date.
I am trying to determine the availability of the person based on the plan start/end date across the tasks.
I was successfully able to do this in Excel using this formula -
=IF(SUMPRODUCT((Data!D:D=\$C\$1)*(A2>=Data!I:I)*(A2<=Data!J:J))>0,"No","Yes")
Where -
Data!D:D has the range containing all the people
\$C\$1 - is the name against which I am searching
A2 = is the date which I am checking if it is falling between two date ranges. The A column contains the days within each month against which I am checking if the person is available or not.
Data!I:I = contains the range of plan start date
Data!J:J = contains the range of plan end date
But when I tried to do this in Google Spreadsheet, It is not giving me the the expected result. The formula I have used is this -
=IF(SUMPRODUCT(('Data'!\$G:\$G=\$B\$4)*(C\$2>='Data'!\$L:\$L)*(C\$2<='Data'!\$M:\$M))>0,"No","Yes"), where - Data!\$G\$G = name range
\$B\$4 = is the name against which I am searching
C\$2 = date row
Data!\$L:\$L = Plan start Date range
Data!\$M:\$M = Plan end Date range