Hi Guys! I'm getting frustrated with my indolence in solving the following problem. Hope some smart good people can help me
I have a spreadsheet in which I add project actual and forecast cost on monthly basis. There is around 500 different projects which, obviously, have different duration, start and end months.
What I need to do is to calculate total value of each project and each month based on actuals and forecast
E.g. Project X started in July and ended in October so its total value in September is SUM(July Actual + August Actual + September Forecast + October Forecast). Any ideas what formula could handle that? I've tried to play with SUM(Actuals) and some OFFSET + MATCH functions but never got the right result...
Below is a simplified table for your reference
<tbody>
</tbody>
I have a spreadsheet in which I add project actual and forecast cost on monthly basis. There is around 500 different projects which, obviously, have different duration, start and end months.
What I need to do is to calculate total value of each project and each month based on actuals and forecast
E.g. Project X started in July and ended in October so its total value in September is SUM(July Actual + August Actual + September Forecast + October Forecast). Any ideas what formula could handle that? I've tried to play with SUM(Actuals) and some OFFSET + MATCH functions but never got the right result...
Below is a simplified table for your reference
Jul-18 Act | Aug-18 Act | Sep-18 Act | Oct-18 Act | Nov-18 Act | Dec-18 Act | Jul-18 For | Aug-18 For | Sep-18 For | Oct-18 For | Nov-18 For | Dec-18 For | Total Act+For | ||
Jul-18 | Project X | 10 | 5 | 5 | 10 | 6 | ??? | |||||||
Aug-18 | Project X | 10 | 6 | 5 | 6 | 11 | 9 | ??? | ||||||
Sep-18 | Project X | 10 | 6 | 11 | 5 | 6 | 10 | 8 | ??? | |||||
Oct-18 | Project X | 10 | 6 | 11 | 8 | 5 | 6 | 10 | 9 | ??? | ||||
Sep-18 | Project Y | 75 | 70 | 80 | 95 | 82 | ??? | |||||||
Oct-18 | Project Y | 75 | 79 | 70 | 82 | 90 | 82 | ??? | ||||||
Nov-18 | Project Y | 75 | 79 | 110 | 70 | 82 | 85 | 82 | ??? | |||||
Dec-18 | Project Y | 75 | 79 | 110 | 82 | 70 | 82 | 85 | 82 | ??? |
<tbody>
</tbody>