summing columns if there is data

[Dave]

For each month of the year, I have a budget column with sales and cost data
(e.g. in column B) and then next to that I have a column where the actual
data goes when the data is available.


Col B Col C Col D
Col E
1m Budget 1m Actual 2m Budget 2m
Actual

Row7 Sales $100 $110 $105

Row9 Labor Cost $40 $55 $60

I want to be set up a formulae to be able to sum the data for all 12 months
for each row using the budget data, but as the actual data gets filled in and
becomes available I want the actual data to be used instead of the budget
data for that month so the total year forecast becomes a combination of
actual data and budget.

For example the original forecast for sales would have been 100 (month 1
budget) + 105 (month 2 budget) = 205 but now the revised sales forecast is
110 (actual month1) + 105 (month 2 budget) = 215. The data runs from column B
through to Y.

Do I use a sumif if a value is in the actual columns and how does this get
set up to check the actuals automatically and sum them instead of the budget
columns to the left if they exist.

Many Thanks!

 

[Franz Verga]

Nel post *Dave* ha scritto:

For each month of the year, I have a budget column with sales and
cost data (e.g. in column B) and then next to that I have a column
where the actual data goes when the data is available.


Col B Col C Col D
Col E
1m Budget 1m Actual 2m Budget 2m
Actual

Row7 Sales $100 $110 $105

Row9 Labor Cost $40 $55 $60

I want to be set up a formulae to be able to sum the data for all 12
months for each row using the budget data, but as the actual data
gets filled in and becomes available I want the actual data to be
used instead of the budget data for that month so the total year
forecast becomes a combination of actual data and budget.

For example the original forecast for sales would have been 100
(month 1 budget) + 105 (month 2 budget) = 205 but now the revised
sales forecast is 110 (actual month1) + 105 (month 2 budget) = 215.
The data runs from column B through to Y.

Do I use a sumif if a value is in the actual columns and how does
this get set up to check the actuals automatically and sum them
instead of the budget columns to the left if they exist.

Many Thanks!

Hi Dave,

Try with this formula (I used just 3 months, but you can extend to 12
months):

=SUM(IF(C4,C4,B4),IF(E4,E4,D4),IF(G4,G4,F4))

where columns B, D and F are for budget data while C, E and G are fo actual
data.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Dave]

Franz, it works like a charm. thanks!

Nel post *Dave* ha scritto:



Hi Dave,

Try with this formula (I used just 3 months, but you can extend to 12
months):

=SUM(IF(C4,C4,B4),IF(E4,E4,D4),IF(G4,G4,F4))

where columns B, D and F are for budget data while C, E and G are fo actual
data.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy