+ Reply to Thread
Results 1 to 15 of 15

Help with summing up a column of mixed numbers and letters

  1. #1
    Registered User
    Join Date
    12-29-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Help with summing up a column of mixed numbers and letters

    Hi, I have a column with the duration of a number of courses written in hours and minutes, and minutes and seconds as in the example below.
    Below the column starts with 4 hours and 35 minutes. And in the last cell you have 58 minutes and 36 seconds.
    Is there a way that I can add the values as they are, in order to get the total duration as a time value (in hours, minutes and seconds), without having to remove the letters?

    Your help will be much appreciated.

    Thanks in advance.


    4h 35m
    3h 9m
    6h 16m
    8h 46m
    58m 36s
    Attached Files Attached Files
    Last edited by oputa ossai; 05-21-2019 at 08:13 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help with summing up a column of mixed numbers and letters

    If they are text strings then one way in a helper column alongside in B1 copied down would be

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Help with summing up a column of mixed numbers and letters

    With your sample data in A1:A5

    Here are a couple approaches:
    Individual transformation: Copy this regular formula down through B5
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Format that cell as [h]:mm:ss;@

    Single-cell summary: This array-formula, completed by holding down CTRL and SHIFT when you press ENTER, converts each cell to time and sums the results:
    Please Login or Register  to view this content.
    The result is: 23:44:36

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  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: Help with summing up a column of mixed numbers and letters

    Hi,

    =SUM(MMULT(0+(0&TRIM(MID(0&A1:A5,FIND({"h","m","s"},0&A1:A5&"00h00m00s")-2,2))),1/{24;1440;86440}))

    Regards
    Click * below if this answer helped

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

  5. #5
    Registered User
    Join Date
    12-29-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Help with summing up a column of mixed numbers and letters

    Thanks for your suggestions, guys. I have a couple of questions though.

    I don't even know where to paste the formula. Where does it go?

    Secondly, the example I gave you with a column that is 5 cells long, was just an example. What I actually have is a column that is almost a 100 cells long. Do your suggestions still apply in this case

  6. #6
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Help with summing up a column of mixed numbers and letters

    First, XOR LX did a nice job of creating the concatenation.
    Second, MMULT is a difficult function to grasp if you don't use it regularly.

    Third...to answer your question....I blended XOR LX's concatenation with SUMPRODUCT to (hopefully) create a formula that's easier to understand AND it can be applied to a larger range.

    This regular formula returns the sum of durations contained in A1:A100
    Please Login or Register  to view this content.
    Does that help?

  7. #7
    Registered User
    Join Date
    12-29-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Help with summing up a column of mixed numbers and letters

    None of the formulas above have worked for me, unfortunately. Ron, yours gave a wrong answer by miles.

  8. #8
    Registered User
    Join Date
    12-29-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Help with summing up a column of mixed numbers and letters

    I've edited the original post to upload the actual excel file. If any one has the time or would be kind enough to have a go, I'd appreciate it. I adjusted the formulas that you all suggested to take into account the fact that it's column D we're dealing with and not column A. And I also adjusted the range from D2 to D73. But still, nothing has worked for me.

  9. #9
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Help with summing up a column of mixed numbers and letters

    Without knowing the value you want to return, it's difficult to know what to fix.
    I used this regular formula in the workbook you posted
    Please Login or Register  to view this content.
    It returned: 7.035972222
    which, when formatted as [h]:mm:ss;@, displays: 162:42:48

    When I calculated each value and summed them, I got that total.
    Can you let us know what you think the total should be?

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Help with summing up a column of mixed numbers and letters

    @ oputa ossai

    Additionally some of the data in column D do not have the appended "m" and in at least one instance it is "M". FIND is case sensitive. These won't solve the current issue, but will avoid other errors.

    That said with XOR LX's formula I get 171:16:48
    Dave

  11. #11
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Help with summing up a column of mixed numbers and letters

    FR: I think you'd need to explain to me how you got 171:16:48

    After replacing FIND with SEARCH in XOR_LX's and my formulas to make them case-insensitive
    Please Login or Register  to view this content.
    the new total is: 7.03597164378631 which formats as 168:51:48.

    Prior to that change, both formulas returned: 168:42:48
    (Oops! I don't know where I got the 162:42:48...Must've been a hardware problem: Loose nut in front of my keyboard!)

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Help with summing up a column of mixed numbers and letters

    If the attached helps the formula (XOR LX's) is in B2. I changed the "M" to "m", typed in the missing "m"s and removed some extra spaces in column D. Please let me know what I am missing.

    Edit BTW I used that formula on the original pre-cleaned data and got 168:42:48 as well.
    Last edited by FlameRetired; 05-22-2019 at 05:21 PM.

  13. #13
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Help with summing up a column of mixed numbers and letters

    Yikes! I'd just...(ahem)..assumed that the data was properly entered. Didn't even notice the missing m's OR the decimal point where "m" should be.

    After I edited those items (left any extra spaces intact) both formulas I posted return the 171:16:48

    Unless the OP can get some control over the data quality, the potential for issues will always exist.

    Thanks for leading off the path into the weeds where the actual data lived.

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Help with summing up a column of mixed numbers and letters

    My pleasure. An interesting puzzle and cool formulas.

  15. #15
    Registered User
    Join Date
    12-29-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Help with summing up a column of mixed numbers and letters

    Hi I've been trying to reply your messages for two days now but the forum wouldn't let me. It kept saying that I had to have created a few posts before I could send a message that has attachments in it or something like that. Anyway, I'm using a different laptop now and it's not showing me that message.
    Last edited by oputa ossai; 05-24-2019 at 04:20 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: 8
    Last Post: 07-07-2018, 03:27 AM
  2. Sort Mixed Data With Periods, Letters, Numbers, Backslashes and Hyphens
    By officemate in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-16-2015, 01:59 PM
  3. vlookup with mixed numbers and letters
    By rolex in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-16-2015, 08:28 AM
  4. How do I sort column with mixed letters and digits?
    By harproblem in forum Excel General
    Replies: 8
    Last Post: 01-16-2015, 06:04 PM
  5. Replies: 4
    Last Post: 03-20-2014, 01:39 PM
  6. [SOLVED] Summing letters that equal numbers
    By M. McGee in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-07-2013, 03:12 PM
  7. Sort mixed numbers/letters
    By V-ger in forum Excel General
    Replies: 1
    Last Post: 11-14-2005, 06:55 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