Help with formula please

[S]

I am using the following formula which basically adds up the value in some
cells.

=SUM(V7,V47,V84,V122,V160,V198,V236,V274,V312)

This works well if all the values are positive (eg £2.80)
If however one or some of the cells are negative (eg -£2.80)
Then it deducts the negative from the total value.
Is there a way to alter the formula so that it only adds the positive
values.

thanks

--


Rent a villa in Turkey

http://www.freewebs.com/turkeyrent/

 

[Dave Mills]

I am using the following formula which basically adds up the value in some
cells.

=SUM(V7,V47,V84,V122,V160,V198,V236,V274,V312)
=sum(Max(V7,0),Max(V47,0),Max(V84,0),Max(V122,0),Max(V198,0),Max(V236,0),Max(V274,0),Max(V312,0))

This works well if all the values are positive (eg £2.80)
If however one or some of the cells are negative (eg -£2.80)
Then it deducts the negative from the total value.
Is there a way to alter the formula so that it only adds the positive
values.

thanks

 

[Bernie Deitrick]

One other way is to bring all your values into a table using link: say, in A1:A10

Then use SUMIF on the table of linked values:

=SUMIF(A1:A10,">0")

Or set up your page so that the values are in a predictable row:
V7, V47, V87, V127 etc to V327 (every 40th row starting at row 7)

Array enter (enter using ctrl-Shift-Enter):
=SUMPRODUCT((MOD(ROW(V1:V327),40)=7)*(V1:V327>0)*(V1:V327))

HTH,
Bernie
MS Excel MVP

 

[Sandy Mann]

One way:

=SUM(((V7>0)*V7)+((V47>0)*V47)+((V84>0)*V84)+((V122>0)*V122)+((V160>0)*V160)+((V198>0)*V198)+((V236>0)*V236)+((V274>0)*V274)+((V312>0)*V312))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Bernie,

=SUMPRODUCT((MOD(ROW(V1:V327),40)=7)*(V1:V327>0)*(V1:V327))

seems to work for me without being array entered.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk