# SUMPRODUCT with dynamic range based entirely on individual cells

1. ## 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

2. ## Re: SUMPRODUCT with dynamic range based entirely on individual cells

Hi, welcome to the forum

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.
Another way to break out your data into helper columns (which will give a MUCH cleaner data set to work with) would be to use a formula like...
=TRIM(MID(SUBSTITUTE(\$b4," ",REPT(" ",LEN(\$b4))),LEN(\$b4)*(COLUMN()-2)+1,LEN(\$b4)))
copied across and down

I will take a look at what you have so far though.

Also, can you include some sample answers (if you have not already done so)?

3. ## Re: SUMPRODUCT with dynamic range based entirely on individual cells

one possible option? assumes % are always +integers of 1-2 digits (can be adjusted if not).

Formula:
`Please Login or Register  to view this content.`

repeated for other columns but have to change hardwired U.S. to Europe etc as you don't have headers aligned to your categories (if you did you could modify to header cell)

I've attached a sample which should convert above to reflect your locale settings.

edit: if you prefer to return % rather than integer either - i.e. 90% rather than 90 either:

a) add /100 to above formula, or

b) use variant of above -- modifying outer SUBSITUTE & MID, and TEXT format:

Formula:
`Please Login or Register  to view this content.`

4. ## Re: SUMPRODUCT with dynamic range based entirely on individual cells

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!

5. ## Re: SUMPRODUCT with dynamic range based entirely on individual cells

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:

6. ## Re: SUMPRODUCT with dynamic range based entirely on individual cells

see attached revision:

7. ## Re: SUMPRODUCT with dynamic range based entirely on individual cells

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 %?

8. ## Re: SUMPRODUCT with dynamic range based entirely on individual cells

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).

There are currently 1 users browsing this thread. (0 members and 1 guests)