Help with Strange Formula

S

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
 
A

Ardus Petus

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

HTH
 
D

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.-
 
S

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
 
B

Bob Phillips

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

--
HTH

Bob Phillips

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

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)
 
Top