• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumifs Comparing Date Ranges

Stephanie

Stephanie

New Member
Hello,

I'm trying to pull back data from a supervisor tracking table - it simply keeps track of employees and who they reported to during certain times of the year with report start and end dates. I am comparing this to a project table which includes project start and end dates. I thought I could do this in Excel 2007 with SUMIFS, but ran into some trouble towards the end of the formula. I know my formula problably won't help a lot, but the gist of it is this:

- Pull back the Supervisor Name during the time period that corresponds to the Project Start and End date
- This is done in the Projects Table and is matched Employee Name to Employee Name
- Basically if the Project Start & Stop Date are 1/15/2013 - 5/1/2013, and you were reporting to Jane Doe 1/1/2013 - 8/31/2013, I would like that to pull back Jane Doe.
- We are not supposed to have individuals move supervisors mid-project - in fact, if something happens, the project is supposed to be re-started just for logging sake, so an error message if, say, the person reported to Jane Doe until 4/30/2013 and then started reporting to another supervisor on 5/1/2013 would be perfect - it would mean that someone forgot to restart the project with the new supervisor split on 5/1!

=SUMIFS(SupervisingHistory[Supervisor],SupervisingHistory[Employee Name],Project_Details[[#This Row],[Employee Name]],(AND(Project_Details[[#This Row],[Start Date]],">"(SupervisingHistory[Start Date],Project_Details[[#This Row],[Start Date]],"<="&SupervisingHistory[End Date]),AND((Project_Details[[#This Row],[Start Date]],">="&SupervisingHistory[Start Date],SupervisingHistory[Start Date]"<="Project_Details[[#This Row],[Start Date]])
 
Hi, Stephanie!
1st argument of SUMIFS function is the range to be summed, you are trying to sum the Supervisor column of table SupervisingHistory, is that right?
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!
 
Here's a sample file - with dummy data - ideally, the first table will simply pull in the applicable supervisor the individual had at the time of the project. If this works, I'd also like to do something similar with effective billing rates.
 

Attachments

Hi Stephanie,

Could not do this without VBA, attached file assumes that there are or were only two supervisors per employee

To test you delete the supervisor from the table and run the macro.

The Macro builds a unique list of employees.

I am sure that the Ninjas will improve on this, but it is a start.

cheers

kanti
 

Attachments

Hi Stephanie,

My pleasure :)

Forgot to mention an observation in your data. You have a few negative "# of days" in the first table, because the "start date" is after the "end date". If this is only in your sample data, then don't mind me :)
 
Thank you for noticing. It is the sample data - the real spreadsheet has actual employee and project names - my dummy version was not produced with the same attention to project detail.
 
You must log in or register to reply here.
Back
Top