Combining IF/Date and Possibly Index/Match
I am trying to create a formula that performs several task in order to yield one value. The formula needs to (a) look for a unique identifier from one sheet to another (index/match?) AND (b) it also needs to look at a date and if it falls within a particular month. If the unique identifier matches and the date range (month/year) is correct, it should fill the cell with the value from the index portion of the formula.
I am trying to find a way to track estimated hours to complete per month across a sheet. I have the project start date, end date, etc hours (total), and a column for avg etc per month (based on project duration). It is simple to calculate/pull the captured values - start date, end date, etc total, etc avg. Where I am struggling is populating the avg etc per month into the months between the start and end date (assume a multi month project). Pulling in the avg per month etc (estimate to complete) is acceptable in this case.
For example - row 1 of the destination sheet should have 167 in the cells for Jan, Feb and March. I assume I will have to write a month/year specific formula for each column. I just need a functioning example for 1 month/year.
Thank you for the help
Raw Data Screenshot:
Help Article Resources
Check out the Formula Handbook template!