# Sumifs with a repeating time time range

#### davideross19

##### New Member
Dear MrExcel forum,

As per many posting, I am lost to understand the resulting instability my formula is producing. I have tried 2 different methods from various posts and both yield the same oscillating summations. I wish to sum the kWh consumption contained in a raw file containing 30min interval data over a complete year (17,520 cells). I wish to sum the data in 1 hour increments (0-24) by calendar months for weekdays only (used: networkdays function). The raw data has the date/time listed in the one cell. I have created a separate column for the time only using MOD(). I have used the following two equations, both yield the same results for a given month.

=SUMIFS(\$F\$6:\$F\$17526,\$D\$6:\$D\$17526,"<="&\$R40,\$D\$6:\$D\$17526,">"&\$R39,\$B\$6:\$B\$17526,">="&S\$38,\$B\$6:\$B\$17526,"<="&S\$39,\$C\$6:\$C\$17526,">0")

where R\$40 = 1/11/2012 (Normally this would be end of the month but have used same day to illustrate with limited data my problem); R39 = 1/11/2012 (First day of the month); S\$38 = 12:00:00 AM and S\$39 = 1:00:00 AM.

Alternatively I have tried (ignoring the month complication for now)

=SUM(IF((VALUE(\$D\$6:\$D\$17526)>=R39)*(VALUE(\$D\$6:\$D\$17526)<=R40)*(\$C\$6:\$C\$17526=1),\$F\$6:\$F\$17526))

Please note this is historical looking data, i.e. the kWh consumption posted at 1:00:00 AM is the sum total kWh consumed in the prevailing 30min interval by the client.

Taking the simple metered data shown below, summing up until 6am, the sum in each 1 hour block ought to be between 5.2-5.4 kWh/hr. If I limit the above equations to this shorter range of numbers I obtain using both methods the same result. I can not understand why I obtain the peaks and troughs (oscillation). I have tried with/without the "=" in the ">=" or vice versa in a consistent manner but this does not change the result. Thank you in advance.

 5.2 1:00:00 AM 2.8 2:00:00 AM 5.2 3:00:00 AM 8 4:00:00 AM 2.6 5:00:00 AM 5.3 6:00:00 AM

<tbody>
</tbody>

<tbody>
</tbody>

 DATE=INT(E6) COL B Weekday COL C TIME=MOD(E6,1) COL D Date & time COL E kWh COL F 1/11/2012 1 12:30:00 AM 1/11/2012 0:30 2.6 1/11/2012 1 1:00:00 AM 1/11/2012 1:00 2.6 1/11/2012 1 1:30:00 AM 1/11/2012 1:30 2.8 1/11/2012 1 2:00:00 AM 1/11/2012 2:00 2.6 1/11/2012 1 2:30:00 AM 1/11/2012 2:30 2.6 1/11/2012 1 3:00:00 AM 1/11/2012 3:00 2.7 1/11/2012 1 3:30:00 AM 1/11/2012 3:30 2.6 1/11/2012 1 4:00:00 AM 1/11/2012 4:00 2.7 1/11/2012 1 4:30:00 AM 1/11/2012 4:30 2.6 1/11/2012 1 5:00:00 AM 1/11/2012 5:00 2.6 1/11/2012 1 5:30:00 AM 1/11/2012 5:30 2.7 1/11/2012 1 6:00:00 AM 1/11/2012 6:00 2.5 1/11/2012 1 6:30:00 AM 1/11/2012 6:30 2.9 1/11/2012 1 7:00:00 AM 1/11/2012 7:00 3.6 1/11/2012 1 7:30:00 AM 1/11/2012 7:30 4.7 1/11/2012 1 8:00:00 AM 1/11/2012 8:00 6.6 1/11/2012 1 8:30:00 AM 1/11/2012 8:30 7.4 1/11/2012 1 9:00:00 AM 1/11/2012 9:00 8.2 1/11/2012 1 9:30:00 AM 1/11/2012 9:30 11.1 1/11/2012 1 10:00:00 AM 1/11/2012 10:00 9.7 1/11/2012 1 10:30:00 AM 1/11/2012 10:30 10 1/11/2012 1 11:00:00 AM 1/11/2012 11:00 9.8 1/11/2012 1 11:30:00 AM 1/11/2012 11:30 10.1 1/11/2012 1 12:00:00 PM 1/11/2012 12:00 10.1 1/11/2012 1 12:30:00 PM 1/11/2012 12:30 11.2

<tbody>
</tbody>

Last edited:

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### Marcelo Branco

##### MrExcel MVP
Hi and welcome to Mr Excel forum

Maybe something like this

 B​ C​ D​ E​ F​ G​ H​ I​ J​ 1​ Date​ Weekday​ Time​ Date/Time​ kWh​ Lower​ Higher​ kWh​ 2​ 01/11/2012​ 1​ 12:30:00 AM​ 01/11/2012 00:30​ 2,6​ 12:00:00 AM​ 1:00:00 AM​ 5,2​ 3​ 01/11/2012​ 1​ 1:00:00 AM​ 01/11/2012 01:00​ 2,6​ 1:00:00 AM​ 2:00:00 AM​ 5,4​ 4​ 01/11/2012​ 1​ 1:30:00 AM​ 01/11/2012 01:30​ 2,8​ 2:00:00 AM​ 3:00:00 AM​ 5,3​ 5​ 01/11/2012​ 1​ 2:00:00 AM​ 01/11/2012 02:00​ 2,6​ 3:00:00 AM​ 4:00:00 AM​ 5,3​ 6​ 01/11/2012​ 1​ 2:30:00 AM​ 01/11/2012 02:30​ 2,6​ 4:00:00 AM​ 5:00:00 AM​ 5,2​ 7​ 01/11/2012​ 1​ 3:00:00 AM​ 01/11/2012 03:00​ 2,7​ 5:00:00 AM​ 6:00:00 AM​ 5,2​ 8​ 01/11/2012​ 1​ 3:30:00 AM​ 01/11/2012 03:30​ 2,6​ 9​ 01/11/2012​ 1​ 4:00:00 AM​ 01/11/2012 04:00​ 2,7​ 10​ 01/11/2012​ 1​ 4:30:00 AM​ 01/11/2012 04:30​ 2,6​ 11​ 01/11/2012​ 1​ 5:00:00 AM​ 01/11/2012 05:00​ 2,6​ 12​ 01/11/2012​ 1​ 5:30:00 AM​ 01/11/2012 05:30​ 2,7​ 13​ 01/11/2012​ 1​ 6:00:00 AM​ 01/11/2012 06:00​ 2,5​ 14​ 01/11/2012​ 1​ 6:30:00 AM​ 01/11/2012 06:30​ 2,9​ 15​ 01/11/2012​ 1​ 7:00:00 AM​ 01/11/2012 07:00​ 3,6​ 16​ 01/11/2012​ 1​ 7:30:00 AM​ 01/11/2012 07:30​ 4,7​ 17​ 01/11/2012​ 1​ 8:00:00 AM​ 01/11/2012 08:00​ 6,6​ 18​ 01/11/2012​ 1​ 8:30:00 AM​ 01/11/2012 08:30​ 7,4​ 19​ 01/11/2012​ 1​ 9:00:00 AM​ 01/11/2012 09:00​ 8,2​ 20​ 01/11/2012​ 1​ 9:30:00 AM​ 01/11/2012 09:30​ 11,1​ 21​ 01/11/2012​ 1​ 10:00:00 AM​ 01/11/2012 10:00​ 9,7​ 22​ 01/11/2012​ 1​ 10:30:00 AM​ 01/11/2012 10:30​ 10​ 23​ 01/11/2012​ 1​ 11:00:00 AM​ 01/11/2012 11:00​ 9,8​ 24​ 01/11/2012​ 1​ 11:30:00 AM​ 01/11/2012 11:30​ 10,1​ 25​ 01/11/2012​ 1​ 12:00:00 PM​ 01/11/2012 12:00​ 10,1​ 26​ 01/11/2012​ 1​ 12:30:00 PM​ 01/11/2012 12:30​ 11,2​

Formula in J2 copied down
=SUMIFS(\$F\$2:\$F\$26,\$C\$2:\$C\$26,1,\$D\$2:\$D\$26,">"&H2,\$D\$2:\$D\$26,"<="&I2)

Hope this helps

M.

#### davideross19

##### New Member
Hi Marcelo, thank you for taking the time to reply. Regrettably I still get the same result in my original spreadsheet. If I copy the above into a new sheet, it works fine!

So I have narrowed it down that the error is caused by the time conversion using MOD(E1,1). When I simply copy over the time value with the values in your file, it works perfectly for all 3 equations.

Last edited:

#### davideross19

##### New Member
Ok fixed this with removing MOD() to =TIME(HOUR(E6),MINUTE(E6),SECOND(E6)). For some unknown reasons even though I pulled down the times in ColH & ColI, a couple of the times gave erroneous numbers so simply copied and pasted over these cells with a format above and change the time.

Replies
1
Views
396
Replies
1
Views
201
Replies
2
Views
187
Replies
3
Views
177
Replies
7
Views
116

1,147,516
Messages
5,741,625
Members
423,674
Latest member
Charles2dodo

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