# SUMIFS wrong for one specific time frame.

#### Aravis

##### New Member
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):
Code:
``=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?

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.

This looks wrong

Code:
``=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
Code:
``=SUMIFS(People!\$J\$3:\$J\$10;People!\$B\$3:\$B\$10;">="&'References PMV'!H1;People!\$C\$3:\$C\$10;">"&'References PMV'!H1)``

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.

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.

Last edited:
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.

Replies
6
Views
207
Replies
1
Views
121
Replies
3
Views
188
Replies
2
Views
74
Replies
2
Views
114

1,203,472
Messages
6,055,610
Members
444,803
Latest member
retrorocket129

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back