1. ok.. I have included a spread sheet of stockmarket data.. [attachment=85292ortfolio_ex.xlsx]

1) I want this to add the (-) and (+) values seperately as well as the sum in (J22) these will be shown in (J23) and (J24) respectively
2) in (H3, 4, 9, 14, 19) I mark a (x) denoting intention to sell
HOPEFULLY we can make these 5 marked lines, change colour and NOT be counted in (D) (I) and (J) columns, and those columns display new values
THEN.. the data in these lines will be shown in respective columns (D25) (I25) and (J25) as
(D25) total purchased price of five marked line items
(I25) total of market value of five marked line items
(J25) LOSS value of five marked line items

*smiles*
I THINK i have uploaded and managed this correctly..

2. The sum of the positive values in J3:J21 is =SUMIF(\$J\$3:\$J\$21,">0") and that of the negative values is =SUMIF(\$J\$3:\$J\$21,"<0")

The sum of the values in D321 where the corresponding cell in column H does NOT contain x is
=SUMIF(\$H\$3:\$H\$21,"<>x",D321)
and the sum of the items where the corresponding cell in column H does contain x is
=SUMIF(\$H\$3:\$H\$21,"x",D321)

Similar for column I.

If you want to exclude the rows with "x" in column H from the sum of positive and negative values in column J, the formulas for J23 and J24 become
=SUMPRODUCT((\$H\$3:\$H\$21<>"x")*(\$J\$3:\$J\$21>0)*\$J\$3: \$J\$21)
and
=SUMPRODUCT((\$H\$3:\$H\$21<>"x")*(\$J\$3:\$J\$21<0)*\$J\$3: \$J\$21)

See attached version.

3. Hans.. you are a God!!

thanks mate.. did exactly what I needed!!

cheers
kio

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•