Sum values based on condition

Patrick020

New Member
Joined
Mar 20, 2018
Messages
15
Hi everyone <o:p></o:p>
<o:p></o:p>
I would like to sum values which are in two columns – but only take values from column B if column C is empty.
<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Here is an example:
<o:p></o:p>
<o:p></o:p>
A<o:p></o:p>
B<o:p></o:p>
C<o:p></o:p>
1<o:p></o:p>
Forecast <o:p></o:p>
Actual <o:p></o:p>
2<o:p></o:p>
Item 1 <o:p></o:p>
40,000<o:p></o:p>
45,000<o:p></o:p>
3<o:p></o:p>
Item 2<o:p></o:p>
50,000<o:p></o:p>
4<o:p></o:p>
Item 3<o:p></o:p>
50,000<o:p></o:p>
5<o:p></o:p>
Item 4<o:p></o:p>
25,000<o:p></o:p>
6<o:p></o:p>
Item 5<o:p></o:p>
30,000<o:p></o:p>
40,000<o:p></o:p>
7<o:p></o:p>
8<o:p></o:p>
Total <o:p></o:p>
210,000<o:p></o:p>

<tbody>
</tbody>
<o:p></o:p>
<o:p></o:p>
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:p></o:p>

Hopefully that is clear - would appreciate any help!
 

Some videos you may like

Excel Facts

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

Nine Zero

Well-known Member
Joined
Mar 10, 2016
Messages
622
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
Joined
Jun 29, 2014
Messages
5,869
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Non-array formula, entered normally:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Forecast </td><td style=";">Actual </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Item 1 </td><td style="text-align: right;;">40,000</td><td style="text-align: right;;">45,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Item 2</td><td style="text-align: right;;">50,000</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Item 3</td><td style="text-align: right;;">50,000</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Item 4</td><td style="text-align: right;;">25,000</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Item 5</td><td style="text-align: right;;">30,000</td><td style="text-align: right;;">40,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Total </td><td style="text-align: right;;">210,000</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet64</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B8</th><td style="text-align:left">=SUM(<font color="Blue">SUMIF(<font color="Red">C2:C6,"",B2:B6</font>),C2:C6</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top