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
Bookmarks