Patrick020
New Member
- Joined
- Mar 20, 2018
- Messages
- 15
Hi everyone <o
></o
>
<o
></o
>
I would like to sum values which are in two columns – but only take values from column B if column C is empty. <o
></o
>
<o
></o
>
To provide some context, Column C is actual spend and Column B is forecast spend. When comparing against budget I want to SUM actual spend where available, but if the data is notavailable then take the forecast spend figure. <o
></o
>
<o
></o
>
Here is an example: <o
></o
>
<o
></o
>
[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD][/TD]
[TD="width: 64, bgcolor: transparent"]A<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]B<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]C<o
></o
>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]1<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Forecast <o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]Actual <o
></o
>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]2<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]Item 1 <o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]40,000<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]45,000<o
></o
>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]3<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]Item 2<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]50,000<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]4<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]Item 3<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]50,000<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]5<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]Item 4<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]25,000<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]6<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]Item 5<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]30,000<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]40,000<o
></o
>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]7<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]8<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]Total <o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"]210,000<o
></o
>
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
<o
></o
>
<o
></o
>
I need a formula in B8 - The answer here is 210,000 as we SUM the Forecast values in B3, B4, B5 (as they have no Actual figures) and the Actual values in C2, C6 (as they have Actuals). <o
></o
>
Hopefully that is clear - would appreciate any help!


<o


I would like to sum values which are in two columns – but only take values from column B if column C is empty. <o


<o


To provide some context, Column C is actual spend and Column B is forecast spend. When comparing against budget I want to SUM actual spend where available, but if the data is notavailable then take the forecast spend figure. <o


<o


Here is an example: <o


<o


[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD][/TD]
[TD="width: 64, bgcolor: transparent"]A<o


[/TD]
[TD="width: 64, bgcolor: transparent"]B<o


[/TD]
[TD="width: 64, bgcolor: transparent"]C<o


[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]1<o


[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Forecast <o


[/TD]
[TD="width: 64, bgcolor: transparent"]Actual <o


[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]2<o


[/TD]
[TD="width: 64, bgcolor: transparent"]Item 1 <o


[/TD]
[TD="width: 64, bgcolor: transparent"]40,000<o


[/TD]
[TD="width: 64, bgcolor: transparent"]45,000<o


[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]3<o


[/TD]
[TD="width: 64, bgcolor: transparent"]Item 2<o


[/TD]
[TD="width: 64, bgcolor: transparent"]50,000<o


[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]4<o


[/TD]
[TD="width: 64, bgcolor: transparent"]Item 3<o


[/TD]
[TD="width: 64, bgcolor: transparent"]50,000<o


[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]5<o


[/TD]
[TD="width: 64, bgcolor: transparent"]Item 4<o


[/TD]
[TD="width: 64, bgcolor: transparent"]25,000<o


[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]6<o


[/TD]
[TD="width: 64, bgcolor: transparent"]Item 5<o


[/TD]
[TD="width: 64, bgcolor: transparent"]30,000<o


[/TD]
[TD="width: 64, bgcolor: transparent"]40,000<o


[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]7<o


[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]8<o


[/TD]
[TD="width: 64, bgcolor: transparent"]Total <o


[/TD]
[TD="width: 64, bgcolor: transparent"]210,000<o


[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
<o


<o


I need a formula in B8 - The answer here is 210,000 as we SUM the Forecast values in B3, B4, B5 (as they have no Actual figures) and the Actual values in C2, C6 (as they have Actuals). <o


Hopefully that is clear - would appreciate any help!