# Problem with sumifs not calculating correctly.

1. ## 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. ## Re: Problem with sumifs not calculating correctly.

Suggest you post a sample workbook.

Regards, TMS

3. ## 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. ## 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

5. ## 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

6. ## Re: Problem with sumifs not calculating correctly.

Change the format of the cells to [h]:mm:ss to include hours.

7. ## 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. ## Re: Problem with sumifs not calculating correctly.

You're welcome.

9. ## Re: Problem with sumifs not calculating correctly.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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