Sum values based on condition

[Patrick020]

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!

 

[Weazel]

maybe something like...

=SUM(IF(C2:C6<>"",C2:C6,B2:B6)) control shift enter

 

[Nine Zero]

if vba is allowed try this

Sub SumStuff()
Dim r As Range
Dim total As Long
total = 0
'change range here for your range if needed
For Each r In Range("B2:B6")
    If r.Offset(, 1).Value = "" Then
        total = total + r.Value
    ElseIf Not r.Offset(, 1) = "" Then
        total = total + r.Offset(, 1).Value
    End If
Next
'change location of sum if needed
Range("B8").Value = total

End Sub

 

[jtakw]

Hi,

Non-array formula, entered normally:

Book1
	A	B	C
1		Forecast	Actual
2	Item 1	40,000	45,000
3	Item 2	50,000
4	Item 3	50,000
5	Item 4	25,000
6	Item 5	30,000	40,000
7
8	Total	210,000
Sheet64

Cell Formulas
Range	Formula
B8	=SUM(SUMIF(C2:C6,"",B2:B6),C2:C6)