Forecast v actual - row addition problem

[Tom Sharrocks]

Hi,

Example:-

Say headers Week 1 to WeeK 6 in columns A1 to F1.
Column range A2:F7 each cell contains a number (say 1).
Each column is summed individually in row A8:F8 (which is the forecast
values), with G 8 summing A8:F8.
Therefore total Forecast, G8 = 36.

In row A9:F9, the Actual values will be input when known.

What I seek is a formula for cell G9 that would sum the values entered into
A9:F9 (actual values when entered) + values from A8:F8 (Forecast values),
but only abstract values from A8:F8 that have not got the corresponding
Actual values entered in row A9:F9.


To clarifiy, say each summed cells A8, B8, C8, D8, E8, F8, contain 6,
therefore total Forecast in G8 equals 36.
Cells A9, B9 contain Actual values 7.
I am seeking a formula in G9 that sums cells A9, B9, C8, D8, E8, F8. and
would reflect total as more Actual values are input. ie G9 would equal
(36-12 from A8:F8)+14 from A9, B9), equals total 38

As each Actual value is input, the corresponding Forecast value is omitted
from the total in G9.

Regards,

Tom

 

[Barb Reinhardt]

Try this:

=SUM(A9:F9)+SUM(IF(ISBLANK(A9:F9),A8:F8))

Commit with CTRL SHIFT ENTER

 

[Tom Sharrocks]

Barb Reinhardt,

Hit the nail right on the head. I am most grateful for your speedy solution,
Thanks & Regards, Tom