+ Reply to Thread
Results 1 to 5 of 5

Formula to sum multiple values in one cell.

  1. #1
    Registered User
    Join Date
    10-20-2017
    Location
    Dublin, Ireland
    MS-Off Ver
    Microsoft Excel for Office 365 MSO (16.0.11425.20200) 32-bit
    Posts
    23

    Question Formula to sum multiple values in one cell.

    Hi guys, I hope you're in good health. I've a question about summing a number of rows, each with a cell with multiple values. It's a strange output file from an aircraft manufacturer that I need to cleanup.

    As you can see, column H has multiple values corresponding to each panel in column G. I'd like to sum these values so I can see time for each task reference in column A.

    It becomes issue in Cell 'G227', i'd like this cell to show 0.01 + 0.01 (0.02) and so on for rest of the column. But I need a solution that doesn't involve counting each cell, or splits the cells so that the reference in column A becomes 'detached' as it where. What I mean to say is, if I sort by column B time, I'd like the values that should 'stick' to Column A's reference to stay with it.

    Is it possible to do this quicker than going through each cell manually? I've attached sheet.
    Attached Files Attached Files

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,496

    Re: Formula to sum multiple values in one cell.

    The only way, IMO, to do this quickly and without having to massage the data, and boy does that data need massaging, is to use a little "VBA" to create a custom function for you. This function would installed in the workbook, so the workbook becomes a .xlsm (macro-enable) workbook.

    Then the new function would be entered into an adjacent column and then copied down the whole table. This could give you a "SUM" of column H values for each cell.

    This function could be used on a single cell, or simply used to address and entire range of cells and give you a sum from the whole range. This means you could get a total in a single cell rather than having to create a whole column of values row by row.

    Is a VBA solution acceptable?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    3,649

    Re: Formula to sum multiple values in one cell.

    Please try at K2

    =SUM(--IFERROR(FILTERXML("<b><a>"&SUBSTITUTE(H2,CHAR(10),"</a><a>")&"</a></b>","//a"),0))
    Attached Files Attached Files
    Last edited by Bo_Ry; 05-09-2020 at 01:02 PM. Reason: Shorten formula

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,496

    Re: Formula to sum multiple values in one cell.

    That's just fabulous.
    Last edited by JBeaucaire; 05-08-2020 at 05:03 PM.

  5. #5
    Registered User
    Join Date
    10-20-2017
    Location
    Dublin, Ireland
    MS-Off Ver
    Microsoft Excel for Office 365 MSO (16.0.11425.20200) 32-bit
    Posts
    23

    Re: Formula to sum multiple values in one cell.

    Quote Originally Posted by Bo_Ry View Post
    Please try at K2

    =SUM(--IFERROR(FILTERXML("<b><a>"&SUBSTITUTE(H2,CHAR(10),"</a><a>")&"</a></b>","//a"),0))
    Hi Bo_Ry,

    As JBeaucaire commented, fabulous! I will now dissect formula and see what each seperate function is. But's it's fixed my problem now.

    JBeaucaire, I will endeavour to learn more about VBA. It's always been a black art to me!

+ 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. [SOLVED] Formula to sum cells with multiple values in each cell
    By benoj2005 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-29-2018, 11:52 AM
  2. Replies: 1
    Last Post: 02-27-2018, 01:36 PM
  3. Multiple values one cell and formula in another
    By MINZGHOR in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-27-2017, 09:20 PM
  4. Multiple line values in a single cell - to use in formula
    By skastroman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-29-2017, 06:31 AM
  5. [SOLVED] Formula to Return cell value based on multiple Max & Min values
    By hecgroups in forum Excel General
    Replies: 10
    Last Post: 08-27-2017, 03:08 PM
  6. [SOLVED] Need countifs formula for multiple values in single cell
    By hmr2662 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-14-2016, 11:02 AM
  7. Replies: 6
    Last Post: 06-08-2010, 09:42 PM

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