+ Reply to Thread
Results 1 to 8 of 8

SUMPRODUCT with dynamic range based entirely on individual cells

  1. #1
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    2013
    Posts
    51

    Exclamation 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
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,991

    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)?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,884

    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: copy to clipboard
    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: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by XLent; 03-08-2019 at 05:36 AM.

  4. #4
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    2013
    Posts
    51

    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. #5
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    2013
    Posts
    51

    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:

    Answer.png

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,884

    Re: SUMPRODUCT with dynamic range based entirely on individual cells

    see attached revision:
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    2013
    Posts
    51

    Re: SUMPRODUCT with dynamic range based entirely on individual cells

    Quote Originally Posted by XLent View Post
    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. #8
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,884

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 01-20-2017, 04:27 PM
  2. [SOLVED] Dynamic calculation based on individual start and end dates
    By Cunner in forum Excel General
    Replies: 7
    Last Post: 01-06-2016, 10:05 AM
  3. VBA Sum dynamic range based on another cells values
    By cudge_usa in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-14-2013, 04:13 AM
  4. How to create a dynamic table based on a range of cells
    By leviathan86 in forum Excel General
    Replies: 1
    Last Post: 01-09-2013, 03:59 PM
  5. [SOLVED] dynamic range specification (based on column header) in sumproduct multiple condition
    By anand_erin in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-28-2012, 10:16 PM
  6. Select dynamic range based on non-empty cells
    By oOarthurOo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2009, 01:42 PM
  7. Coping cells based on dynamic range
    By acs013 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-17-2009, 08:44 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1