SUMIFS wrong for one specific time frame.

[Aravis]

I am trying to make an excel sheet where I summarize values happening between different hours. So for hour 02:00 I want to summarize everything happening between 02:00-03:00. That would include time frames 02:00-03:00, 01:00-04:00 etc.

The values I currently have are the ones seen below, which are in a sheet called "People".

	B	C	J
2	Start time	End Time	Value to be summarized
3	00:00	01:00	27.2172
4	01:00	02:00	27.3431
5	02:00	03:00	27.4698
6	03:00	04:00	27.5973
7	04:00	05:00	27.7255
8	05:00	06:00	27.8542
9	06:00	07:00	27.9835
10	00:00	00:00	28.1132

<tbody>
</tbody>

I am trying to summarize these values in another sheet called "References PMV". I do this with the following formula in the I column (going from H1 to H10):

=SUMIFS(People!$J$3:$J$10;People!$B$3:$B$10;"<="&'References PMV'!H1;People!$C$3:$C$10;">"&H1)

The following is the result I get. As you can see the values are correct except for at 05:00. Here it looks like the value have been summarized twice, and I really can't understand why this happens. I have triedd with different values in the J column. I have tried with time frame 04:00-06:00 instead of 05:00-06:00 in the B and C columns above, I have tried with 05:00-06:00 in different rows. The value for 05:00 is always summarized twice. I am so confused.

	H	I
1	00:00	27.2
2	01:00	27.3
3	02:00	27.5
4	03:00	27.6
5	04:00	27.7
6	05:00	55.6
7	06:00	28.0
8	07:00	0.0
9	08:00	0.0
10	09:00	0.0

<tbody>
</tbody>

Does anyone have any suggestions as to what might cause this?
Or an alternative way of summarizing values for the different times so I can avoid the miscalculation?

 

[Gerald Higgins]

I tried replicating this, but I don't get the same results.

Here's what I get...

00:00	27.2172
01:00	27.3431
02:00	27.4698
03:00	27.5973
04:00	27.7255
05:00	27.8542
06:00	27.9835
07:00	0
08:00	0
09:00	0
09:00	0

<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>
</tbody>

Comment - as your source data is shown to 4 decimal places, and many of the source values are a little over 0.1 apart from each other, it would be more helpful to show your results to the same precision, i.e. 4 decimal places, at least for the purposes of this thread.
You can always change the formating to something else once this problem has been resolved.

 

[Special-K99]

This looks wrong

=SUMIFS(People!$J$3:$J$10;People!$B$3:$B$10;"<="&'References PMV'!H1;People!$C$3:$C$10;">"&H1)

You've left the worksheet name out of H1 on the second reference

=SUMIFS(People!$J$3:$J$10;People!$B$3:$B$10;">="&'References PMV'!H1;People!$C$3:$C$10;">"&'References PMV'!H1)

 

[Gerald Higgins]

Ah that could be it, I tried replicating it but just ignoring all the different sheet references, and doing it all on the same sheet.

 

[steve the fish]

I reckon its more likely to be a rounding issue. I can replicate it if:

=People!B8>'References PMV'!H6

but still shows as 5:00. I added 0.000000000000001 to People!B8 and the sumifs produces results that you see.

 

[Aravis]

I think I have managed to solve the problem, though I don't understand how it makes a difference.

Under format cells - numbers: Originally all my hours were categorized as time of type 13:30. I changed the category to custome with type tt:mm. After that the calculations were magically correct.