How to return the Number of shares for a given stock code for a given portfolio

[aray]

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.

[Miguel Zapico]

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:C100)
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
>
>