Formula to calculate total values from a mix of progressive actual and forecast data

[heniek00]

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

[TABLE="width: 1213"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Jul-18 Act[/TD]
[TD]Aug-18 Act[/TD]
[TD]Sep-18 Act[/TD]
[TD]Oct-18 Act[/TD]
[TD]Nov-18 Act[/TD]
[TD]Dec-18 Act[/TD]
[TD]Jul-18 For[/TD]
[TD]Aug-18 For[/TD]
[TD]Sep-18 For[/TD]
[TD]Oct-18 For[/TD]
[TD]Nov-18 For[/TD]
[TD]Dec-18 For[/TD]
[TD]Total Act+For[/TD]
[/TR]
[TR]
[TD]Jul-18[/TD]
[TD]Project X[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD] ???[/TD]
[/TR]
[TR]
[TD]Aug-18[/TD]
[TD]Project X[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD] ???[/TD]
[/TR]
[TR]
[TD]Sep-18[/TD]
[TD]Project X[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD] ???[/TD]
[/TR]
[TR]
[TD]Oct-18[/TD]
[TD]Project X[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]11[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD] ???[/TD]
[/TR]
[TR]
[TD]Sep-18[/TD]
[TD]Project Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]70[/TD]
[TD]80[/TD]
[TD]95[/TD]
[TD]82[/TD]
[TD] ???[/TD]
[/TR]
[TR]
[TD]Oct-18[/TD]
[TD]Project Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]75[/TD]
[TD]79[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]70[/TD]
[TD]82[/TD]
[TD]90[/TD]
[TD]82[/TD]
[TD] ???[/TD]
[/TR]
[TR]
[TD]Nov-18[/TD]
[TD]Project Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]75[/TD]
[TD]79[/TD]
[TD]110[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]70[/TD]
[TD]82[/TD]
[TD]85[/TD]
[TD]82[/TD]
[TD] ???[/TD]
[/TR]
[TR]
[TD]Dec-18[/TD]
[TD]Project Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]75[/TD]
[TD]79[/TD]
[TD]110[/TD]
[TD]82[/TD]
[TD][/TD]
[TD][/TD]
[TD]70[/TD]
[TD]82[/TD]
[TD]85[/TD]
[TD]82[/TD]
[TD] ???[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

"It's total value in September is

SUM(July Actual + August Actual + September Forecast + October Forecast)"


Nope.

It's total value in September is just

July Actual + August Actual + September Forecast + October Forecast

You don't need SUM(), you're already SUMming them by using the plus sign.
It's like saying (and this happens an AWFUL lot)

SUM(3+4)

It's simply 3+4

You dont need the SUM()

 

[heniek00]

That's a fair comment but still does not answer the question about a formula to calculate those sums for different rows/months.
Anyone please?