+ Reply to Thread
Results 1 to 9 of 9

Problem with sumifs not calculating correctly.

  1. #1
    Registered User
    Join Date
    09-26-2015
    Location
    Colorado, USA
    MS-Off Ver
    Office 10
    Posts
    11

    Problem with sumifs not calculating correctly.

    Hello, I'm trying to calculate to total number of hours for each month.
    On one sheet, I have three columns. Start , stop, and total. Start and Stop are full date/time columns. Total is a number.
    Once this goes live, there will be many, many entries for each month, not just one line.

    Example:

    (SHEET "MF_CREMATIONS", C4 - E4)
    Start End Total
    1/25/15 7:13 PM 1/25/15 10:10 PM 2:57:00
    2/25/15 10:30 PM 2/26/15 1:10 AM 2:40:00
    3/25/15 1:01 PM 3/25/15 3:01 PM 2:00:00
    4/25/15 1:01 PM 4/25/15 3:01 PM 2:00:00
    5/25/15 1:01 PM 5/25/15 3:01 PM 2:00:00
    6/25/15 1:01 PM 6/25/15 3:01 PM 2:00:00
    7/25/15 1:01 PM 7/25/15 3:01 PM 2:00:00
    8/25/15 1:01 PM 8/25/15 3:01 PM 2:00:00
    9/25/15 1:01 PM 9/25/15 3:01 PM 2:00:00
    10/25/15 1:01 PM 10/25/15 3:01 PM 2:00:00
    11/25/15 1:01 PM 11/25/15 3:01 PM 2:00:00
    12/25/15 1:01 PM 12/25/15 3:01 PM 2:00:00

    On my reporting sheet, I have the sumifs capturing for each month.

    (this row is hidden , but is the calculating cells in formula...B5, C5...) 1/1/2015 2/1/2015 3/1/2015 4/1/2015 5/1/2015 6/1/2015 7/1/2015 8/1/2015 9/1/2015 10/1/2015 11/1/2015 12/1/2015
    JANUARY FEBRUARY MARCH APRIL MAY JUNE JULY AUGUST SEPTEMBER OCTOBER NOVEMBER DECEMBER
    57:00.0 40:00.0 00:00.0 00:00.0 00:00.0 00:00.0 00:00.0 00:00.0 00:00.0 00:00.0 00:00.0 00:00.0

    The problem is that it's leaving off the hours.

    Report Example:
    JANUARY
    57:00.0 ....should be 2:57:00.0

    FEBRUARY
    40:00.0 ....Should be 2:40:00.0

    formula in that cell: =SUMIFS(MF_CREMATIONS!$E$4:$E$499,MF_CREMATIONS!$C$4:$C$499,">="&B5,MF_CREMATIONS!$C$4:$C$499,"<"&C5)

    What am I doing wrong, that is causing it to leave off the hours?

  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
    44,091

    Re: Problem with sumifs not calculating correctly.

    Suggest you post a sample workbook.

    Regards, TMS
    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
    Registered User
    Join Date
    09-26-2015
    Location
    Colorado, USA
    MS-Off Ver
    Office 10
    Posts
    11

    Re: Problem with sumifs not calculating correctly.

    It won't let me upload my workbook. It says that it exceeds the file size limitation.

  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Problem with sumifs not calculating correctly.

    Try removing some of the data, maybe not all is needed to solve the task. Or use dropbox or similar
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  5. #5
    Registered User
    Join Date
    09-26-2015
    Location
    Colorado, USA
    MS-Off Ver
    Office 10
    Posts
    11

    Re: Problem with sumifs not calculating correctly.

    I have literally posted the total content of my spreadsheet in text format. There isn't anything more to it, nor anything to cut out.


    DROP BOX: https://www.dropbox.com/s/1cmsbspoyq...ISED.xlsx?dl=0
    Last edited by graphicjunkie; 09-26-2015 at 04:05 PM.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Problem with sumifs not calculating correctly.

    Change the format of the cells to [h]:mm:ss to include hours.
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Registered User
    Join Date
    09-26-2015
    Location
    Colorado, USA
    MS-Off Ver
    Office 10
    Posts
    11

    Re: Problem with sumifs not calculating correctly.

    Yup! I hate it when you stare at something so long, you are blind to the answer. Thanks shg!

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Problem with sumifs not calculating correctly.

    You're welcome.

  9. #9
    Registered User
    Join Date
    09-26-2015
    Location
    Colorado, USA
    MS-Off Ver
    Office 10
    Posts
    11

    Re: Problem with sumifs not calculating correctly.

    Continued in new thread....
    Last edited by graphicjunkie; 09-30-2015 at 03:39 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: 12
    Last Post: 06-24-2015, 02:00 PM
  2. Sumifs not calculating correctly
    By Sonal518 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-17-2015, 07:44 AM
  3. [SOLVED] Sumifs Formula not working correctly
    By jamaal09 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-01-2013, 09:36 PM
  4. sumifs not adding up correctly
    By rrcrossman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-07-2013, 05:13 PM
  5. sumifs how to fill in the data correctly
    By mikosquak in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 07-28-2013, 01:32 AM
  6. Sumifs formula isn't working correctly
    By gjergji in forum Excel General
    Replies: 5
    Last Post: 09-20-2012, 05:15 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