Need help , Sumifs not working

[erhitesh]

Requirement
I have to create Resource Planning document

Purpose
To allocate projects to resources who have availability ( Allocation < 100% ) during a period of time


Layout:

Worksheets:

1) Dashboard - ( Not a topic of discussion here)
2) Resourcing - Assigning projects to Team members (Columns - Resource Name, Project Name, PM, Start Date, End Date and Allocation
3) Calculations - I have a Table to Calculate Monthly allocation for each resource ( Column names - Team Member, Week1 -Starting Jan 1 2015 and subsequent cells in the row are Jan 8 2015 (Week2 ) with 7 days getting added everytime we move rightwards )
4) Names - I have the List of names of Team Members, Managers and Projects

Now I want SUMIFS function to look out for Resourcing sheet and calculate all the allocations for the particular week (as per allocations to different projects and defined timelines <start dat,end date>)

Formula m using

=SUMIFS(Resourcing!$F:$F,Resourcing!$A:$A,Calculations!$A3,Resourcing!$D:$D,"<="&B$2,Resourcing!$E:$E,">="&B$2&"+7")

[TABLE="width: 657"]
<colgroup><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Week1[/TD]
[TD]Week2[/TD]
[TD]Week3[/TD]
[TD]Week4[/TD]
[TD]Week5[/TD]
[TD]Week6[/TD]
[TD]Week7[/TD]
[/TR]
[TR]
[TD]Team Members[/TD]
[TD]1-Jan-15[/TD]
[TD]8-Jan-15[/TD]
[TD]15-Jan-15[/TD]
[TD]22-Jan-15[/TD]
[TD]29-Jan-15[/TD]
[TD]5-Feb-15[/TD]
[TD]12-Feb-15[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0

[/TD]
[/TR]
</tbody>[/TABLE]

But it is returning 0 , although I have allocation for the weeks , for ex (Resourcing sheet)

[TABLE="width: 711"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Resource Name[/TD]
[TD]Project Name[/TD]
[TD]PM[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Allocation[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]ADT[/TD]
[TD]MNO[/TD]
[TD]1/1/2015[/TD]
[TD]4/30/2015[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]ADT2[/TD]
[TD]DCF[/TD]
[TD]1/1/2015[/TD]
[TD]10/31/2015[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]

PFB the link to the file
https://drive.google.com/file/d/0B0ToU0gBsIEbSXdoNkg1LU9aNjA/view?usp=sharing


Thanks in advance!!!!!

 

[elmer007]

I may be wrong, but I believe you'll have better luck if you change
">="&B$2&"+7"
to
">="&B$2+7

 

[erhitesh]

I may be wrong, but I believe you'll have better luck if you change
">="&B$2&"+7"
to
">="&B$2+7

yeah !! Thanks

 

[erhitesh]

Hi Can you please help me in this

http://www.mrexcel.com/forum/excel-questions/873752-chart-multiple-criteria.html#post4238427