Help with Strange Formula

[scr5jo]

Hi Guys,

I am trying to make a budget spreadsheet and want to get fancy with it
I have a macro that converts a figure when i have paid it to add som
brackets around it ie < 10.00 >

Now I want to only add up the fiqures that arn't in these brackets.

Any ideas?

e.g.

10.00
10.00
<10.00>
<20.00>
10.0

 

[Ardus Petus]

SUM function will ignore text values, so you can write:
=SUM(A1:A5)
which will produce 30.00

HTH

 

[DL]

It is the wrong way (imho). You need to add another field near to your
amounts as (for example): paid or Payment status etc. and fill it up
with 1 or 0 or with some check box (paid / nope). It will be the correct
way of maintaining this small DB.

D.-

 

[scr5jo]

Hi Ardus,

it's not a text value, it's just the way I formatted the cell. If I ru
my macro again, it will convert it back to normal. Here is the marco s
you can see :

Sub Show_Paid()
If ActiveCell.NumberFormat = "#,##0.00" Then
Selection.NumberFormat = "< #,##0.00 >"
ElseIf ActiveCell.NumberFormat = "< #,##0.00 >" Then
ActiveCell.NumberFormat = "#,##0.00"
End If
End Sub


DL : I tried a second column but it's messy - I have my columns a
Jan, Feb etc with carry forward balances and so to work over 2 column
is a pain.

What I am hopeing to get is a nested SUB (IF) statement that ca
identify the formula type and then not add up the <> ones. I a
struggeling to find a format command in Excel though( plenty in VBA)

thanks
Stev

 

[Bob Phillips]

=SUMIF(A:A,">0")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

and =SUMIF(A:A,"<0") for those in the brackets

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)