# Sum values based on condition

#### Patrick020

##### New Member
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>
 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

<tbody>
</tbody>
<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!

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### Weazel

##### Well-known Member
maybe something like...

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

#### Nine Zero

##### Well-known Member
if vba is allowed try this

Code:
``````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

##### Well-known Member
Hi,

Non-array formula, entered normally:

Replies
3
Views
119
Replies
1
Views
30
Replies
3
Views
151
Replies
2
Views
128
Replies
0
Views
188

1,127,266
Messages
5,623,714
Members
415,983
Latest member
MusicMan

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back