ignoring negative numbers
 

excel 2002 / windows xp

PROBLEM
I want to automatically add up a column of four values some of which could
be negative values.
I want to be able to add the values together with the rule that any negative
values can be excluded.
BACKGROUND
Each of the four values has been created through a formula. This formula is
a multiplication of 2 values which is then subject to minus calculation.
example 1
formula = £0.0270 x 4444 - £12.00 = £107.98
example 2
formula = £0.0270 x 412 - £12.00 = - £0.876 (negative number)
example 3
formula = £0.0270 x 678 - £12.00 = £6.306
example 4
= formula £0.0270 x 234 - £12.00 = - £5.682 (negative number)
When I add them up with my limited knowledge of excel I get £107.728 - what
I want to get is £114.286

thank you for taking a look at my question

Dave

ignoring negative numbers
 

Hi,

assuming the values are in a1 - a4 use this

=SUM(IF(A1:A40,A1:A4))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"threademporium" wrote:

excel 2002 / windows xp

PROBLEM
I want to automatically add up a column of four values some of which could
be negative values.
I want to be able to add the values together with the rule that any negative
values can be excluded.
BACKGROUND
Each of the four values has been created through a formula. This formula is
a multiplication of 2 values which is then subject to minus calculation.
example 1
formula = £0.0270 x 4444 - £12.00 = £107.98
example 2
formula = £0.0270 x 412 - £12.00 = - £0.876 (negative number)
example 3
formula = £0.0270 x 678 - £12.00 = £6.306
example 4
= formula £0.0270 x 234 - £12.00 = - £5.682 (negative number)
When I add them up with my limited knowledge of excel I get £107.728 - what
I want to get is £114.286

thank you for taking a look at my question

Dave

ignoring negative numbers
 

=SUMIF(A1:A4, "0")
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"threademporium" wrote in message
...
excel 2002 / windows xp

PROBLEM
I want to automatically add up a column of four values some of which could
be negative values.
I want to be able to add the values together with the rule that any
negative
values can be excluded.
BACKGROUND
Each of the four values has been created through a formula. This formula
is
a multiplication of 2 values which is then subject to minus calculation.
example 1
formula = £0.0270 x 4444 - £12.00 = £107.98
example 2
formula = £0.0270 x 412 - £12.00 = - £0.876 (negative number)
example 3
formula = £0.0270 x 678 - £12.00 = £6.306
example 4
= formula £0.0270 x 234 - £12.00 = - £5.682 (negative number)
When I add them up with my limited knowledge of excel I get £107.728 -
what
I want to get is £114.286

thank you for taking a look at my question

Dave

ignoring negative numbers
 

thank you - worked fine

"Bernard Liengme" wrote:

=SUMIF(A1:A4, "0")
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"threademporium" wrote in message
...
excel 2002 / windows xp

PROBLEM
I want to automatically add up a column of four values some of which could
be negative values.
I want to be able to add the values together with the rule that any
negative
values can be excluded.
BACKGROUND
Each of the four values has been created through a formula. This formula
is
a multiplication of 2 values which is then subject to minus calculation.
example 1
formula = £0.0270 x 4444 - £12.00 = £107.98
example 2
formula = £0.0270 x 412 - £12.00 = - £0.876 (negative number)
example 3
formula = £0.0270 x 678 - £12.00 = £6.306
example 4
= formula £0.0270 x 234 - £12.00 = - £5.682 (negative number)
When I add them up with my limited knowledge of excel I get £107.728 -
what
I want to get is £114.286

thank you for taking a look at my question

Dave

ignoring negative numbers
 

Thank you - worked fine

"threademporium" wrote:

excel 2002 / windows xp

PROBLEM
I want to automatically add up a column of four values some of which could
be negative values.
I want to be able to add the values together with the rule that any negative
values can be excluded.
BACKGROUND
Each of the four values has been created through a formula. This formula is
a multiplication of 2 values which is then subject to minus calculation.
example 1
formula = £0.0270 x 4444 - £12.00 = £107.98
example 2
formula = £0.0270 x 412 - £12.00 = - £0.876 (negative number)
example 3
formula = £0.0270 x 678 - £12.00 = £6.306
example 4
= formula £0.0270 x 234 - £12.00 = - £5.682 (negative number)
When I add them up with my limited knowledge of excel I get £107.728 - what
I want to get is £114.286

thank you for taking a look at my question

Dave