Combining IF/Date and Possibly Index/Match

Brian Biffle

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:

Destination/Formula Sheet:

Paul Newcome

Try this...

=INDEX(COLLECT({ETC Average Column}, {Unique ID Column}, @cell = [Unique ID]@row, {Start Date Column}, AND(IFERROR(MONTH(@cell), 0) <= 1, IFERROR(YEAR(@cell), 0) <= 2022), {End Date Column}, AND(IFERROR(MONTH(@cell), 0) >= 1, IFERROR(YEAR(@cell), 0) >= 2022)), 1)

Paul Newcome

I'm not sure I completely follow what you are wanting to accomplish, but it seems to me like you may be wanting an INDEX/COLLECT.

=INDEX(COLLECT({ETC Average Column}, {Start Date Column}, AND(IFERROR(MONTH(@cell), 0) <= 1, IFERROR(YEAR(@cell), 0) <= 2022), {End Date Column}, AND(IFERROR(MONTH(@cell), 0) >= 1, IFERROR(YEAR(@cell), 0) >= 2022)), 1)

Brian Biffle

Hi @Paul Newcome - one quick observation. I do not believe your formula takes into account that the Unique ID must match to point smartsheet to the correct row. For example, there could be multiple rows with the same start and end month; the unique id is what allows the populating of each cell to be client/project specific.

is there a way to work that into your formula?

Let me try to quickly restate the goal in a series of If statements.

IF the unique id matches And If the Month falls within designated range And If the Year is XXXX, insert the ETC Avg for the corresponding row with the matching unique id.

To pull back the ETC AVG for Row 1 into a cell, you would need the Unique ID to match between the sheets and for the Start Date to have a month value of January and Year Value of 2022.

I have to use the month/year criteria as the additional layer of filtering to drop the data into the correct month/year column for each row.

Thanks for the help!

Paul Newcome

Try this...

=INDEX(COLLECT({ETC Average Column}, {Unique ID Column}, @cell = [Unique ID]@row, {Start Date Column}, AND(IFERROR(MONTH(@cell), 0) <= 1, IFERROR(YEAR(@cell), 0) <= 2022), {End Date Column}, AND(IFERROR(MONTH(@cell), 0) >= 1, IFERROR(YEAR(@cell), 0) >= 2022)), 1)

Brian Biffle

Thank you Paul

Paul Newcome

Happy to help. 👍️