summing formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ron Woolley - 28 Jan 2006 06:13 GMT

I've built a workbook to keep up with purchase/sales of Stocks, to keep
up with long & short term Capital/Gains for Tax records.

The way that the sheet is built is: A1 thru G2 are used for entering
stock purchases; name, date bought, price, # of shares with sum formula
for "Total Investment Amount (G2)". Cells H2 thru L2 are used when the
Stock is sold (Sale date, number of stocks, price and "Total Investment
Amount(L2)"). The last cell, M2, I want to use to show the Capital
Gain/Loss for that stock sale.

My problem or question is that I don't want Cell M2 to sum unless L2
has value of >0. I've tried the =SUMIF formula and I can't it to work.
I can't seem to find a formula that would work.

Any suggestions?
Thanks in advance,
Ron

Signature

Ron Woolley

Reply to this Message

paul - 28 Jan 2006 06:57 GMT

just a normal if i think,=if(L2>0,sum formula(),"")

Signature

paul
remove nospam for email addy!

> I've built a workbook to keep up with purchase/sales of Stocks, to keep
> up with long & short term Capital/Gains for Tax records.
[quoted text clipped - 13 lines]
> Thanks in advance,
> Ron

Reply to this Message

Bob Phillips - 28 Jan 2006 13:16 GMT

=SUMIF(L2:L200,">0",M2:M200)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

> I've built a workbook to keep up with purchase/sales of Stocks, to keep
> up with long & short term Capital/Gains for Tax records.
[quoted text clipped - 19 lines]
> Ron Woolley's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30904
> View this thread: http://www.excelforum.com/showthread.php?threadid=505977

Reply to this Message

Ron Woolley - 28 Jan 2006 17:40 GMT

Thanks everyone for the ideas.

I finally got the formula to work.
I was placing the () in the wrong place.

the final formula that worked in instance was:
*=IF(L2>0,SUM(L2-G2),0)* and in the cells that I have formatted as
Accounting, if l2 =$0.00 M2 comes back with $ -. If l2 is >0, then it
will come back as $100.00 (Gain) and ($100.00), which is what I was
looking for.

I knew I was close, but... just looking at it too long...or it could be
that I was brain dead..
Ron

Signature

Ron Woolley

Reply to this Message