+ Reply to Thread
Results 1 to 7 of 7

Formula to sum cells with multiple values in each cell

  1. #1
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Formula to sum cells with multiple values in each cell

    Hi

    Is anyone able to help come up with a formula that will total cells with more than one value in each cell.

    Basically trying to plan out a wall I'm tiling in the bathroom and want a formula that will sum up cells with more than one value in each cell as each cell will have a length and a width value so I want to know the sum of the values ending in "H" for height and a sum of the values ending in "W" for width.

    Simple example below

    21w x 10h 66w x 10h 21w x 10h
    21w x 30h 66w x 30h 21w x 30h
    21w x 10h 66w x 10h 21w x 10h

    So for each column I want the total height and for each row the total length. Results would be 50H for each column and 108W for each row

    Thankyou!
    Last edited by benoj2005; 07-29-2018 at 12:03 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula to sum cells with multiple values in each cell

    There are none with L here - do you mean H? So, what is your expected result from the sample data?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Formula to sum cells with multiple values in each cell

    Without knowing more, I am going to question your logic. If this is to calcualte the total number or tiles to use then you will get wrong results by adding up length and width then multiplying.

    The way you phrased your question, you have asked for

    (21+21+21+66+66+66+21+21+21) = 324 and (10+30+10+10+30+10+10+30+10) = 150, then multiplied to give total area, 324*150=48600

    Where it looks like you more likely want to add up the product of each cell (21*10)+(21*30)+(21*10)+(66*10)+(66*30)+(66*10)+(21*10)+(21*30)+(21*10)

    Which would be 210+630+210+660+1980+660+210+630+210=5400

    Please clarify which of these results you expect from your simple example.

    edit:-

    This formula should work either way, full version to get product of all cells, or split in half to get sum of width / height.

    =SUMPRODUCT(--TRIM(LEFT(SUBSTITUTE(A2:C4,"w",REPT(" ",10)),10)),--TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2:C4,"h","")," ",REPT(" ",10)),10)))
    Last edited by jason.b75; 07-29-2018 at 09:35 AM.

  4. #4
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2019
    Posts
    197

    Re: Formula to sum cells with multiple values in each cell

    Another formula option,

    and, similar to jason.b75's formula result : 5400

    =SUMPRODUCT(IMREAL(SUBSTITUTE(SUBSTITUTE(A2:C4,"w x ","+"),"h","i")),IMAGINARY(SUBSTITUTE(SUBSTITUTE(A2:C4,"w x ","+"),"h","i")))

    Regards
    Bosco

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to sum cells with multiple values in each cell

    Just a bit shorter than jason's
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by AlKey; 07-29-2018 at 10:59 AM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  6. #6
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2019
    Posts
    197

    Re: Formula to sum cells with multiple values in each cell

    Another shorter formula :

    =SUMPRODUCT(LEFT(A2:C4,FIND("w",A2:C4)-1)*MID(SUBSTITUTE(A2:C4,"h",),FIND("x",A2:C4)+2,99))

    Regards
    Bosco

  7. #7
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Re: Formula to sum cells with multiple values in each cell

    Quote Originally Posted by jason.b75 View Post
    Without knowing more, I am going to question your logic. If this is to calcualte the total number or tiles to use then you will get wrong results by adding up length and width then multiplying.

    The way you phrased your question, you have asked for

    (21+21+21+66+66+66+21+21+21) = 324 and (10+30+10+10+30+10+10+30+10) = 150, then multiplied to give total area, 324*150=48600

    Where it looks like you more likely want to add up the product of each cell (21*10)+(21*30)+(21*10)+(66*10)+(66*30)+(66*10)+(21*10)+(21*30)+(21*10)

    Which would be 210+630+210+660+1980+660+210+630+210=5400

    Please clarify which of these results you expect from your simple example.

    edit:-

    This formula should work either way, full version to get product of all cells, or split in half to get sum of width / height.

    =SUMPRODUCT(--TRIM(LEFT(SUBSTITUTE(A2:C4,"w",REPT(" ",10)),10)),--TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2:C4,"h","")," ",REPT(" ",10)),10)))
    Cheers, just hacked this apart into
    Please Login or Register  to view this content.
    for the widths and
    Please Login or Register  to view this content.
    for the heights
    Last edited by benoj2005; 07-29-2018 at 12:00 PM.

+ 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: 13
    Last Post: 05-24-2018, 01:18 AM
  2. Replies: 1
    Last Post: 02-27-2018, 01:36 PM
  3. [SOLVED] How do I separate multiple values in one cell in to multiple new cells?
    By boxedIn in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-01-2014, 08:56 AM
  4. Multiple Cells values - formula to see which cells added together equal zero?
    By rodders0223 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-15-2013, 11:09 AM
  5. [SOLVED] Formula to write multiple values to a cell based on value of other cells
    By crazyjockey in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-05-2012, 09:32 PM
  6. Sum values from multiple cells - multiple lookup values in single cell
    By taxdept in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2012, 04:12 PM
  7. Replies: 2
    Last Post: 01-09-2012, 02:36 PM

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