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".

[TABLE="width: 326"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Start time[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Value to be summarized[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]00:00[/TD]
[TD="align: center"]01:00[/TD]
[TD="align: center"]27.2172[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]01:00[/TD]
[TD="align: center"]02:00[/TD]
[TD="align: center"]27.3431[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]02:00[/TD]
[TD="align: center"]03:00[/TD]
[TD="align: center"]27.4698[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]03:00[/TD]
[TD="align: center"]04:00[/TD]
[TD="align: center"]27.5973[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]04:00[/TD]
[TD="align: center"]05:00[/TD]
[TD="align: center"]27.7255[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]05:00[/TD]
[TD="align: center"]06:00[/TD]
[TD="align: center"]27.8542[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]06:00[/TD]
[TD="align: center"]07:00[/TD]
[TD="align: center"]27.9835[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]00:00[/TD]
[TD="align: center"]00:00[/TD]
[TD="align: center"]28.1132[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl66, width: 64, align: right"][/TD]
[TD="class: xl66, width: 64, align: center"]H[/TD]
[TD="class: xl65, width: 64, align: center"]I[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: center"]1[/TD]
[TD="class: xl66, width: 64, align: center"]00:00[/TD]
[TD="class: xl65, width: 64, align: center"]27.2[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]2[/TD]
[TD="class: xl66, align: center"]01:00[/TD]
[TD="class: xl65, align: center"]27.3[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]3[/TD]
[TD="class: xl66, align: center"]02:00[/TD]
[TD="class: xl65, align: center"]27.5[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]4[/TD]
[TD="class: xl66, align: center"]03:00[/TD]
[TD="class: xl65, align: center"]27.6[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]5[/TD]
[TD="class: xl66, align: center"]04:00[/TD]
[TD="class: xl65, align: center"]27.7[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]6[/TD]
[TD="class: xl66, align: center"]05:00[/TD]
[TD="class: xl65, align: center"]55.6[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]7[/TD]
[TD="class: xl66, align: center"]06:00[/TD]
[TD="class: xl65, align: center"]28.0[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]8[/TD]
[TD="class: xl66, align: center"]07:00[/TD]
[TD="class: xl65, align: center"]0.0[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]9[/TD]
[TD="class: xl66, align: center"]08:00[/TD]
[TD="class: xl65, align: center"]0.0[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]10[/TD]
[TD="class: xl66, align: center"]09:00[/TD]
[TD="class: xl65, align: center"]0.0[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 128"]
<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]00:00[/TD]
[TD="class: xl65, width: 64, align: right"]27.2172[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]01:00[/TD]
[TD="class: xl65, align: right"]27.3431[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]02:00[/TD]
[TD="class: xl65, align: right"]27.4698[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]03:00[/TD]
[TD="class: xl65, align: right"]27.5973[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]04:00[/TD]
[TD="class: xl65, align: right"]27.7255[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]05:00[/TD]
[TD="class: xl65, align: right"]27.8542[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]06:00[/TD]
[TD="class: xl65, align: right"]27.9835[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]07:00[/TD]
[TD="class: xl65, align: right"]0
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]08:00[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]09:00[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]09:00
[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

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.