Open stock postion formula question

ajr123 · Apr 13th, 2002, 05:28 PM

I have a worksheet which extracts buy and sell info that refrence a specific stock. The buys are in one column and the sells are in antoher. However they do not line up becasue of the way they extracted What I am looking to do is creat a formula that will reference the symbol in and return the sum of the buys and sells.There may be several lines of both so I would need it to sum all buys and sells that reference that symbol.
Any help on this would be great.
Thanks!

daleyman · Apr 13th, 2002, 05:29 PM

you might want to run a pivot table for this information.

Yogi Anand · Apr 13th, 2002, 05:36 PM

or A DSUM function will also do the job nicely!

ajr123 · Apr 13th, 2002, 06:04 PM

I have tried the Pivot tables but in order to do what I am looking to do its too slow, I was hoping their was a formula that would do the trick.

DSUM sounds good but I am not sure how to make this work any ideas.
Thanks

Yogi Anand · Apr 13th, 2002, 08:58 PM

Hi ajr123:
=DSUM(database,field,criteria)

database is your table reference where your data is located
field is the number of column from the left of your database which will be used for summing up
criteria is the range of cells that define the criteria that will be used to select which cells would be summed up .. this range must lie outside the table -- first row will have the field name(s), and the row(s)below will have the criteria associated with each field name
HTH!

Dave Patton · Apr 13th, 2002, 10:01 PM

You could use Sumif

=SUMIF(rSym,A3,rBuy)-SUMIF(rSym,A3,rSell)

Apr 13th, 2002, 05:29 PM	#2
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	you might want to run a pivot table for this information. __________________ <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;">*DALEY :P**</td></table>

Apr 13th, 2002, 08:58 PM	#5
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi ajr123: =DSUM(database,field,criteria) database is your table reference where your data is located field is the number of column from the left of your database which will be used for summing up criteria is the range of cells that define the criteria that will be used to select which cells would be summed up .. this range must lie outside the table -- first row will have the field name(s), and the row(s)below will have the criteria associated with each field name HTH! __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 13th, 2002, 05:28 PM	#1
ajr123 New Member Join Date: Apr 2002 Location: Adam Posts: 9	I have a worksheet which extracts buy and sell info that refrence a specific stock. The buys are in one column and the sells are in antoher. However they do not line up becasue of the way they extracted What I am looking to do is creat a formula that will reference the symbol in and return the sum of the buys and sells.There may be several lines of both so I would need it to sum all buys and sells that reference that symbol. Any help on this would be great. Thanks!

Apr 13th, 2002, 05:36 PM	#3
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	or A DSUM function will also do the job nicely!

Apr 13th, 2002, 06:04 PM	#4
ajr123 New Member Join Date: Apr 2002 Location: Adam Posts: 9	I have tried the Pivot tables but in order to do what I am looking to do its too slow, I was hoping their was a formula that would do the trick. DSUM sounds good but I am not sure how to make this work any ideas. Thanks

Apr 13th, 2002, 10:01 PM	#6
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	You could use Sumif =SUMIF(rSym,A3,rBuy)-SUMIF(rSym,A3,rSell)