Combining IF/Date and Possibly Index/Match

Brian Biffle
Brian Biffle ✭✭
edited 05/26/21 in Formulas and Functions

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:

image.png

Destination/Formula Sheet:

image.png


Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!