+ Reply to Thread
Results 1 to 8 of 8

How to sum two different substr in a column

  1. #1
    Registered User
    Join Date
    09-07-2015
    Location
    Germany
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Cool How to sum two different substr in a column

    Hello,
    I have a question regarding summing up some values in a column, which are separated by a slash. How can this be achieved???

    The column looks like:

    4 / 6
    5 / 2
    2 / 3
    4

    2 / 1
    3

    I want these cells to be summed up like:
    20 / 12

    This means, empty cells should be considered as "0". Cells, which are not separated, should be added to the "first" sum.
    Personally, I would split up this column to two different columns - but it's an official template form. I may not change the design, but the functionality.
    I hope, to get some help here

    Thank you in advance,
    C.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to sum two different substr in a column

    with text to column and after that a sumformula.

    it would be better if you added your file, so we don't have to reproduce the data.

    see the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: How to sum two different substr in a column

    However, if you cant use Text to Column because you "cant change the design" then this UDF will do it.
    Please Login or Register  to view this content.
    If someone has helped you then please add to their Reputation

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to sum two different substr in a column

    Hi.

    Are the digits only ever single digits, as is the case with all in the examples you give?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to sum two different substr in a column

    Data Range
    A
    1
    4 / 6
    2
    5 / 2
    3
    2 / 3
    4
    4
    5
    6
    2 / 1
    7
    3
    8
    20/12

    Data Range
    A
    8
    =SUM(IFERROR(LEFT(SUBSTITUTE(A1:A7,"/",REPT(" ",100)),100)*1,0))&"/"&SUM(IFERROR(RIGHT(SUBSTITUTE(A1:A7&IF(ISERR(FIND("/",A1:A7)),"/",""),"/",REPT(" ",100)),100)*1,0))

    Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.<br><br>- Array Formula - Requires CTRL+SHIFT+ENTER


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  6. #6
    Registered User
    Join Date
    09-07-2015
    Location
    Germany
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Re: How to sum two different substr in a column

    Thank you for all your suggestions!

    Quote Originally Posted by :) Sixthsense :) View Post
    [...]
    Data Range
    A
    8
    =SUM(IFERROR(LEFT(SUBSTITUTE(A1:A7,"/",REPT(" ",100)),100)*1,0))&"/"&SUM(IFERROR(RIGHT(SUBSTITUTE(A1:A7&IF(ISERR(FIND("/",A1:A7)),"/",""),"/",REPT(" ",100)),100)*1,0))
    Hello SixthSense,
    thank you very much for your response! The formula, you posted, does almost anything - I wanted!
    I have a German Edition of MS Office 2010 installed and therefore, I had to replace the function names, you used, and I replaced the "," by ";".
    Alas, my "localized" version of the formula sums the left values correctly (20), but the sum of th eright values returns 0.

    Please Login or Register  to view this content.
    Do you have any idea, what's wrong?
    Kind regards,
    C.

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to sum two different substr in a column

    Attached a formula file, so that while opening the file excel will auto convert all the english formula to your language.

    Also note that the formula is an Array formula which requires CTRL+SHIFT+ENTER instead of normal enter.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-07-2015
    Location
    Germany
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Re: How to sum two different substr in a column

    Again THANK YOU!

    AWESOME!

    THANK YOU!

+ 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: 7
    Last Post: 08-09-2015, 05:07 PM
  2. Help me to use Substr function in Excel
    By SriramDorai in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 08-28-2013, 06:05 AM
  3. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-09-2012, 08:56 AM
  4. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel General
    Replies: 4
    Last Post: 04-07-2012, 09:14 AM
  5. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel General
    Replies: 5
    Last Post: 04-06-2012, 12:02 PM
  6. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-06-2012, 11:18 AM
  7. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-06-2012, 11:13 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