+ Reply to Thread
Results 1 to 11 of 11

Sum function not working

  1. #1
    Forum Contributor
    Join Date
    07-28-2015
    Location
    london
    MS-Off Ver
    Microsoft Office 365
    Posts
    116

    Sum function not working

    Hi everyone,

    I was trying to sum up a duration of time but excel is not letting me get the answer I need by using the Sum function. Please see below What I'm trying to achieve.

    I have 2 sheets; the first sheet is the area where I analyse the data and the second sheet is where the data is coming from. From the second sheet say in column B2:B1000 is where the duration of time coming from each row in a format of 00:00:00.

    In sheet 1, I will have a cell (any cell) where the formula is =sum('sheet2'!B2:B10000) but gives me a result of 00:00:00 even with changing the time format from nearly everything.

    I managed to get the result by doing this formula =sum(sheet2'!B2:B1000*24) and that's fine.

    But now the problem is, it will give me the Hours but not the minutes, instead it is showing as Hours then fraction of the hour as an example 4.93 as 4:55:38 (HH:MM:SS) as the total duration of time.

    For me to achieve the format HH:MM:SS, I will need to apply a formula to another cell say =A2/24 to get the HH:MM:SS and format it.

    My question is, is there a formula where I can sum up the duration of time without separately creating another cell to divide it by 24 just to get the format HH:MM:SS?

    I have seen many tutorials that the sum function should work to add the times, but it never worked for me.

    I managed to achieve it by doing the steps outlined above but trying to eliminate the last step where I need to add another cell to divide the total duration of time by 24 then format the cell to get HH:MM:SS.

    Any help is highly appreciated.

    Thanks in advance.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,669

    Re: Sum function not working

    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    16,101

    Re: Sum function not working

    In the absence of a sample file, my first guess is that your times are being stored as text strings and not real date/time serial numbers. The SUM() function ignores text (including numbers stored as text). Math operators (like *) attempt to convert text strings into a number. I would guess that when you multiply by 24, Excel is able to convert the time/numbers stored as text to real numbers while performing the multiplication operation, which then allows the SUM() function to sum up the resulting numbers.

    A quick test for this potential scenario is to use the ISTEXT() function on the cells suspected of containing a number stored as text.

    If it turns out that the cells contain text strings, here are strategies that are often used to convert numbers stored as text to real numbers: https://support.microsoft.com/en-us/...1-c5bad0f0a885 For example, you could try *1 (instead of *24) and see if that allows you to sum up the times.

    Did I guess right?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    3,923

    Re: Sum function not working

    Maybe try =SUM(Sheet2!B2:B1000*24)/24 or --SUM(Sheet2!B2:B1000).

  5. #5
    Forum Contributor
    Join Date
    07-28-2015
    Location
    london
    MS-Off Ver
    Microsoft Office 365
    Posts
    116

    Re: Sum function not working

    Hi everyone,

    Thanks for looking into this.

    MrShorty, I looked at the article and tried few things around but no luck. And also, it won't be a step I will go to if ever I managed to get things working as the idea from the source data is just to download, copy and paste to the template I'm working on as an easy way for the user to analyse the data.

    Josephteh, the formula is working as per mentioned above but I need to put another in a different cell with time format then divide it by 24 to get the actual result I'm looking for as the sum function won't work if it done directly as you instructed too, It's giving me an answer of 00:00:00.

    I have attached a sample file on what I'm trying to achieve.

    On sheet Wk1 cell A2:1000 is data source which is formatted as HH:MM:SS.

    Then in sheet named Data cell B6 I have the formula =SUM('Wk1'!A2:A10000*24) and gets the total sum which is correct but not in the format I need (I have tried all possible formatting to get the HH:MM:SS) please let me know if there's something I'm missing to correct.

    In cell B7 I have the formula =B6/24 to get the format I require which is HH:MM:SS.

    If I can eliminate the cell B7 to have the answer I need by just having the answer in cell B6, then it would be great.

    Thanks in advance.
    Attached Files Attached Files

  6. #6
    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. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,931

    Re: Sum function not working

    Do you mean this?

    =TEXT(SUM('Wk1'!A2:A10000*24)/24,"hh:mm:ss")

    Or better this:

    =SUM('Wk1'!A2:A10000*24)/24

    with the cell formatted to h:mm:ss.
    Attached Files Attached Files
    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  7. #7
    Forum Contributor
    Join Date
    07-28-2015
    Location
    london
    MS-Off Ver
    Microsoft Office 365
    Posts
    116

    Re: Sum function not working

    Hi AligW,

    Thank you very much for your help.

    =SUM('Wk1'!A2:A10000*24)/24 with the cell formatted to h:mm:ss is the solution I'm looking for.

  8. #8
    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. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,931

    Re: Sum function not working

    You're welcome.

  9. #9
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    3,923

    Re: Sum function not working

    Quote Originally Posted by sickreto View Post
    =SUM('Wk1'!A2:A10000*24)/24 with the cell formatted to h:mm:ss is the solution I'm looking for.
    This is the same formula I suggested!
    Last edited by AliGW; 10-30-2022 at 07:52 AM. Reason: Please DON'T quote unnecessarily!

  10. #10
    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. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,931

    Re: Sum function not working

    It is, Joseph, but the OP clearly also needed the advice about cell formatting to make it work fully.

  11. #11
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    3,923

    Re: Sum function not working

    I would have thought OP knows how to do cell formatting when he can specify the hh:mm:ss formatting.

+ 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. How to combine the WORKDAY function with an IF function to build a working Gantt chart.
    By roomaggoo in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 07-07-2018, 11:54 AM
  2. Replies: 0
    Last Post: 07-07-2018, 04:13 AM
  3. [SOLVED] SUM/COUNTA Function Not Working On Cell Data Generated from SUM Function
    By pexeterblue43 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-15-2018, 03:47 PM
  4. Replies: 1
    Last Post: 06-02-2015, 01:38 AM
  5. IF statement not working with text function and edate function.
    By joshnathan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-04-2013, 10:26 AM
  6. [SOLVED] Using the Split function with another function incorporated is not working correctly
    By highlystrung in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-06-2013, 03:51 PM
  7. Replies: 4
    Last Post: 12-21-2010, 04:34 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