Sumifs with a repeating time time range

davideross19

New Member
Joined
May 5, 2014
Messages
3
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.201:00:00 AM
2.802:00:00 AM
5.203:00:00 AM
8.004:00:00 AM
2.605:00:00 AM
5.306: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/2012112:30:00 AM1/11/2012 0:302.6
1/11/201211:00:00 AM1/11/2012 1:002.6
1/11/201211:30:00 AM1/11/2012 1:302.8
1/11/201212:00:00 AM1/11/2012 2:002.6
1/11/201212:30:00 AM1/11/2012 2:302.6
1/11/201213:00:00 AM1/11/2012 3:002.7
1/11/201213:30:00 AM1/11/2012 3:302.6
1/11/201214:00:00 AM1/11/2012 4:002.7
1/11/201214:30:00 AM1/11/2012 4:302.6
1/11/201215:00:00 AM1/11/2012 5:002.6
1/11/201215:30:00 AM1/11/2012 5:302.7
1/11/201216:00:00 AM1/11/2012 6:002.5
1/11/201216:30:00 AM1/11/2012 6:302.9
1/11/201217:00:00 AM1/11/2012 7:003.6
1/11/201217:30:00 AM1/11/2012 7:304.7
1/11/201218:00:00 AM1/11/2012 8:006.6
1/11/201218:30:00 AM1/11/2012 8:307.4
1/11/201219:00:00 AM1/11/2012 9:008.2
1/11/201219:30:00 AM1/11/2012 9:3011.1
1/11/2012110:00:00 AM1/11/2012 10:009.7
1/11/2012110:30:00 AM1/11/2012 10:3010
1/11/2012111:00:00 AM1/11/2012 11:009.8
1/11/2012111:30:00 AM1/11/2012 11:3010.1
1/11/2012112:00:00 PM1/11/2012 12:0010.1
1/11/2012112:30:00 PM1/11/2012 12:3011.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
Joined
Aug 23, 2010
Messages
16,907
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
May 5, 2014
Messages
3
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
Joined
May 5, 2014
Messages
3
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top