Formula help for budgeting spreadsheet

[kulprit]

Hi All,

I have a budget table which I need a little bit of formula help with. So when I select the option of Paid I would like it to deduct the value from the total providing me with the remaining balance. Please see below example of how I would like it to work;


[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Company[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Paid/Unpaid[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Utility 1[/TD]
[TD="align: right"]50[/TD]
[TD]Paid[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Utility 2[/TD]
[TD="align: right"]100[/TD]
[TD]Paid[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Utility 3[/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Total[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Remaining[/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Can anyone help with the formula of removing the figure from amount when it says Paid from the total figure in order to provide me with the remaining figure?

Many thanks,

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]

1​

[/td][td]Company[/td][td]Amount[/td][td]Paid/Unpaid[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

2​

[/td][td]Utility 1[/td][td]

50​

[/td][td]Paid[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

3​

[/td][td]Utility 2[/td][td]

100​

[/td][td]Paid[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

4​

[/td][td]Utility 3[/td][td]

150​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

6​

[/td][td]Remaining[/td][td="bgcolor:#CCFFFF"]

150​

[/td][td][/td][td="bgcolor:#CCFFFF"]C5: =SUMIF(D2:D4, "<>Paid", C2:C4)[/td][/tr]
[/table]

 

[kulprit]

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[TD="bgcolor: #C0C0C0"]

D​

[/TD]
[TD="bgcolor: #C0C0C0"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

1​

[/TD]
[TD]

1​

[/TD]
[TD]Company[/TD]
[TD]Amount[/TD]
[TD]Paid/Unpaid[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]

2​

[/TD]
[TD]Utility 1[/TD]
[TD]

50​

[/TD]
[TD]Paid[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]

3​

[/TD]
[TD]Utility 2[/TD]
[TD]

100​

[/TD]
[TD]Paid[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD]

4​

[/TD]
[TD]Utility 3[/TD]
[TD]

150​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

5​

[/TD]
[TD]

6​

[/TD]
[TD]Remaining[/TD]
[TD="bgcolor: #CCFFFF"]

150​

[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFFF"]C5: =SUMIF(D2:D4, "<>Paid", C2:C4)[/TD]
[/TR]
</tbody>[/TABLE]

Thank you so much! I spent so much time trying to figure this out using the wrong formulas haha. I can see why you have the MVP status

 

[shg]

You're welcome.