Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
aray
 
Posts: n/a
Default How to return the Number of shares for a given stock code for a given portfolio


If I have 2 stocks A & B in a portfolio and the same stocks A and B are
in other portfolios. How can I return the correct number of shares I
own for a given stock in a given portfolio?

The data set is arranged by portfolio name and then the name of the
stock and then the number of shares.

I am trying to call the respective stock in the given portfolio so as
to return the number of shares therein.

I tried to use a sumif function which works if the stock is unique to a
portfolio. However if the same stock is in 2 portfolios I get back the
total number of shares.

Can someone suggest a solution that returns the correct number of
shares for a given stock for a given portfolio.


--
aray
------------------------------------------------------------------------
aray's Profile: http://www.excelforum.com/member.php...o&userid=34547
View this thread: http://www.excelforum.com/showthread...hreadid=543144

  #2   Report Post  
Posted to microsoft.public.excel.misc
Miguel Zapico
 
Posts: n/a
Default How to return the Number of shares for a given stock code for a gi

You may use SUM as an array formula. In you example, if you have your
porfolios in A1:A100, your stocks in B1:B100 and the shares in C1:C100 you
can use:
=SUM((A1:A100="porfolio")*(B1:B100="stock")*C1:C10 0)
Enter the formula with CTRL+SHIFT+ENTER, change the ranges and values as
appropiate.

Hope this helps,
Miguel.

"aray" wrote:


If I have 2 stocks A & B in a portfolio and the same stocks A and B are
in other portfolios. How can I return the correct number of shares I
own for a given stock in a given portfolio?

The data set is arranged by portfolio name and then the name of the
stock and then the number of shares.

I am trying to call the respective stock in the given portfolio so as
to return the number of shares therein.

I tried to use a sumif function which works if the stock is unique to a
portfolio. However if the same stock is in 2 portfolios I get back the
total number of shares.

Can someone suggest a solution that returns the correct number of
shares for a given stock for a given portfolio.


--
aray
------------------------------------------------------------------------
aray's Profile: http://www.excelforum.com/member.php...o&userid=34547
View this thread: http://www.excelforum.com/showthread...hreadid=543144


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I retain a zero as the first number in a postal code? Laureen Excel Discussion (Misc queries) 7 February 28th 06 06:34 PM
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
Change case...help please Terry Excel Worksheet Functions 14 October 2nd 05 12:29 PM
how do i create an access code sheet to number my office files an. dana New Users to Excel 1 February 12th 05 04:49 AM
Format the Cell into a telephone number but using country code Chris Quinn Excel Discussion (Misc queries) 1 January 27th 05 04:51 PM


All times are GMT +1. The time now is 03:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"