SUMPRODUCT with dynamic range based entirely on individual cells

Hi experts,

Having spent hours browsing for a solution here and on other pages, I figured it was time to try my luck with a post. I have enclosed a detailed description of the problem.

Basically, I have a column with names of investors and then a subsequent column with data on the composition of their portfolios of stocks. However, data on the stocks of the portfolios and the share invested in each stock is combined into one single cell, rather than spread across multiple cells (see below).

Column A Column B
Investor 1 Stock 1/5%/500,25:Stock 2/10%/1000,50:Stock 3/85%/8004
Investor 2 Stock 1/30%/30000:Stock 5/20%/20000/Stock 8/50%/50000

I am looking for a formula to sum the amounts invested in U.S., European, and Asian stocks (using the percentages, not the numerical values), respectively, for each portfolio, based on a categorization index (so stock 1 might be European, while stock 2 is Asian, etc.).

So far, I have worked out how to extract the stock name and the % invested in each (formulas appear from the file), however, I cannot get the SUMPRODUCT formula to work properly (again, my closest attempt appears from the file).

I am aware that one possible solution is to spread the data across multiple columns using TextToColumns and then combine a SUMPRODUCT with a SUMIF, however, I need a solution that does not involve adding helper columns or additional cells.

Any help will be greatly appreciated, thank you in advance.

Best,
Phil

Thanks for the fast reply and solution XLent

Indeed, the percentages will always be positive integers. However, they can be of more than 1-2 digits (for instance, 10.315% might be invested in stock 1).

Regarding the headers not being aligned to categories, I am not sure I understand you entirely. Could you provide an example?

Thanks!

Thanks a lot FDibbins

I did in fact considered something similar, however, preferably I'd like to avoid the helper columns altogether.

Regarding the answers, they should appear from the file. In simple terms, what I am looking for is the values below:

Answer.png

see attached revision:

Originally Posted by XLent

see attached revision:

Worked perfectly XLent! Thank you very, very much!

One last thing; is there an easy way for the formulas to return the name of a stock rather than the %, if one of the stocks in the portfolio does not exist in the index? For instance, if a portfolio contained, say, stock 13, could the formula return "Stock 13", rather than the %?

Not alongside the %, at least not without distorting data types.

To be honest, depending on how many "exception" stocks you need to conceivably handle alongside the fact you're using XL2013 that could prove quite convoluted & inefficient to the extent you would want to either use helpers, or a custom function (VBA).