Facing an issue with SUMIFS

[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, Jan-15, Feb - 15 and so on )
4) Names - I have the List of names of Team Members, Managers and Projects


Logic (That I am trying to work on)

On Calculations worksheet I am trying to Apply SUMIFS below each Month to capture allocation for the resource for that month using the data on Resourcing

The function I have used

=SUMIFS(Resourcing!$F:$F,Resourcing!$A:$A,Calculations!$A2,Resourcing!$D:$D,"<="&B$1,Resourcing!$E:$E,">="&B$1)

But this is not going to be accurate since I am checking End date with First date of the month

Can anyone help me create more accurate function ?

 

[Jonmo1]

To get the last day of the month of a given date, use EOMONTH

So say B1 is your given date...
EOMONTH(B1,0) gives you the last day of the same month.

Hope that helps.

 

[erhitesh]

To get the last day of the month of a given date, use EOMONTH

So say B1 is your given date...
EOMONTH(B1,0) gives you the last day of the same month.

Hope that helps.

Yeah I tried it too but doesnt go with the function as I want to check if the month lies within the date range

Can there be a better way to calculate the allocation per month

 

[Jonmo1]

To apply it in the sumifsifs function,

Try

=SUMIFS(Resourcing!$F:$F,Resourcing!$A:$A,Calculations!$A2,Resourcing!$D:$D,"<="&EOMONTH(B$1,0),Resourcing!$E:$E,">="&B$1)

This will sum column F, where colA = A2, Col D is <= last day of month of date in B1, And col E is >= B1

 

[erhitesh]

To apply it in the sumifsifs function,

Try

=SUMIFS(Resourcing!$F:$F,Resourcing!$A:$A,Calculations!$A2,Resourcing!$D:$D,"<="&EOMONTH(B$1,0),Resourcing!$E:$E,">="&B$1)

This will sum column F, where colA = A2, Col D is <= last day of month of date in B1, And col E is >= B1

That worked perfect, Thank you so so much

 

[Jonmo1]

You're welcome.