Task details -Summary formula required to calculate weekly hours with multiple criteria

Hi Experts,

Looking for help on a Excel file which is used to capture daily tasks on multiple projects.
The Sheet has 2 tabs, the first is Task details & Second is Weekly Summary.

The Task details is a manual update sheet where multiple members fill in their tasks they work on. One member could be doing tasks on multiple projects and he/she has to fill the category of effort hrs expended (Billable/Non-Billable)

The WeeklySummary has a table with formulas to extract Effort hours expended by any member (which is a manual input). The formula gives the results for all the projects any member has worked on. Currently, the formula extracts effort hours unconstrained to any date ranges (i.e. it picks up all hours for that resource, projectwise without any constraint on the dates)

However, i am looking to add a date range (i.e. days in a week) to extract the hours and also need to get the Category of hours. Also, looking for a graph to depict the Billable Vs Non-Billable trend.

Kindly help with a suitable formula. Thanks in advance

Thanks,
Mahesh

Hi,
See attached.
Also created a table for your graph
A2 and down =IFERROR(INDEX(TaskDetails!$A$2:$A$35,AGGREGATE(15,6,ROW(TaskDetails!$A$2:$A$35)/(MATCH(TaskDetails!$A$2:$A$35,TaskDetails!$A$2:$A$35,0)=ROW(TaskDetails!$A$1:$A$35)),ROWS(TaskDetails!$A$1:A1))),"")

B2-C2 and down:
=COUNTIFS(TaskDetails!$A$2:$A$35,$A2,TaskDetails!$F$2:$F$35,B$1)


Not sure this is what you want...

With Excel2010, you can avoid those array formulae:

in C2:
=IFERROR(INDEX(TaskDetails!$C:$C, AGGREGATE(15,6,ROW(TaskDetails!$D$2:$D$1000)/($B$2=TaskDetails!$D$2:$D$1000),ROWS(C$2:C2))),"")

In D2:
=IFERROR(INDEX(TaskDetails!$E$2:$E$1000,MATCH(1,INDEX(($B$2=TaskDetails!$D$2:$D$1000)*($C2=TaskDetails!$C$2:$C$1000),0),0)),"")

and in E2:
=IFERROR(INDEX(TaskDetails!$F$2:$F$1000,MATCH(1,INDEX(($B$2=TaskDetails!$D$2:$D$1000)*($C2=TaskDetails!$C$2:$C$1000),0),0))&"","")

I do not understand what you want in a7... Is it just: =WEEKNUM(A3)

Just noticed the date range bit.

Formulae modified. see sheet.

You're welcome and thanks!