# Excel Question

I hope this makes sense so bear with me. I have one sheet that has a list of about 70 stocks. Based on the calculations on this sheet some stocks have been coded a buy, sell or nothing. On another sheet I have a trade proposal sheet that I want to print out. I want to write a function so that all the buys from the other sheet are listed together and all the sells are listed together then subtotaled and netted. I havent been able to figure out a way to use a vlookup or related function so that it lists multiple stocks without also listing blank lines. Let me know if that makes sense–if not I’ll try again.

just do 2 sumif functions…Sumif(A4:z4 “=buy”, A3:z3) - Sumif(A4:z4, “=sell”, az:z3) That should work…

pivot table

miker2800 Wrote: ------------------------------------------------------- > just do 2 sumif functions…Sumif(A4:z4 “=buy”, > A3:z3) - Sumif(A4:z4, “=sell”, az:z3) > > That should work… The thing is, I want to list the individual securities as well as the subtotals. For example on sheet 1 column a lists buy or sell column b lists the security name. Sheet 1: a1 = buy a2 = blank, neither buy or sell a3 = buy How do I do a function for column a in sheet 2 so that it lists the security name without row 2 in sheet 2 showing up blank? In this case, I would want o sheet two a1 to show sheet one b1 and sheet two a2 to show sheet one b3. Make sense? jpm. I have never done a pivot table. Know of any good resources to teach me?

Select all the data, then go to the data menu and select pivot table - it sounds like this would summarize the information nicely. as guides: http://office.microsoft.com/en-us/excel/HA010346331033.aspx http://www.youtube.com/watch?v=7zHLnUCtfUk

jpm351 Wrote: ------------------------------------------------------- > Select all the data, then go to the data menu and > select pivot table - it sounds like this would > summarize the information nicely. > > as guides: > > http://office.microsoft.com/en-us/excel/HA01034633 > 1033.aspx > > http://www.youtube.com/watch?v=7zHLnUCtfUk Ok I got it. Kind of. I have the Pivot Table but it still shows all the securities regardless of whether they are buy or sells or nothing. I want to create two pivot tables one for the buys and one for the sales. Is there a way to incorporate an If function into this? I see how you can manually pick which rows you want displayed but that almost defeats the whole purpose of trying to automate this.

Do pivot tables automatically refresh when the data is changed? I didn’t think they did (so someone tell me how if they do please!) If not you could produce a sheet that has three extra columns to the left of the stock list (one each for Buy, “blank” and sell). Then use a nested if and countif function for each rating. Something like =IF(E5=“Buy”,COUNTIF(E\$5:E5,“Buy”),"") Then on a couple of other tabs you’d just need to use vlookups for each rating. something like =VLOOKUP(A22,\$A\$5:\$E\$14,4,FALSE)