SUMIFS - Multiple Criteria of a Time Range for a Specific Date

[EnergySaver]

All,

I am a novice on this site ... with this as my first post! ... I searched for SUMIFS posts that would have answered my question, but alas, did not find an answer so have created this post to get your help.

My Need: To sum-up the total kilo-watts being consumed on a specific day for a specific time frame

My problem: I can get a SUMIFS formula to work with a specific date OR time BUT NOT when I try a specific date with a from-time and to-time.

The raw data is in a tab by itself with over 10,000 rows which basically looks like the following table:​

[TABLE="width: 500"]
<tbody style="border-collapse: collapse; width: auto;">[TR]
[TD]B.date
[/TD]
[TD]B.time
[/TD]
[TD]B.kW
[/TD]
[/TR]
[TR]
[TD]12/15/2017
[/TD]
[TD]04:52:18
[/TD]
[TD]126.283
[/TD]
[/TR]
[TR]
[TD]12/15/2017
[/TD]
[TD]04:53:18
[/TD]
[TD]240.146
[/TD]
[/TR]
[TR]
[TD]12/15/2017
[/TD]
[TD]04:54:18
[/TD]
[TD]428.409
[/TD]
[/TR]
</tbody>[/TABLE]

I have a separate "summary" tab where I put in the date and then the time range (from and to) and hope to see Total kW but I am getting 0 as my total; here is what my summary tab looks like: ​

[TABLE="width: 500"]
<tbody style="border-collapse: collapse; width: auto;">[TR]
[TD="align: center"][/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Date
[/TD]
[TD]From Time
[/TD]
[TD]To Time
[/TD]
[TD]Total kW
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]12/15/2017
[/TD]
[TD]04:50:00
[/TD]
[TD]04:52:18
[/TD]
[TD]SUMIFS formula = 0
[/TD]
[/TR]
</tbody>[/TABLE]

Here are the details concerning my SUMIFS formula ...​

• I created a Named-range for each column in the raw data tab (B.date; B.time; and B.kW)
• =SUMIFS(B.kW,B.Time,D3) returns the correct value of 126.283
• But the minute I add any Boolean logic or more criteria - I get a returned value of 0
• I tried: =SUMIFS(B.kW,B.time,"<="&D3) and the returned value was 0<strike style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"></strike>
  
• I tried: =SUMIFS(B.kW,B.time,">="&C3,B.time,"<="&D3,B.date,B3) and the returned value was 0
  

<strike style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike>

Any and all help will be gratefully appreciated ... Thank You.​

P.S. I am running Excel 2016 on a Windows 10 OS

 

[Aladin Akyurek]

=SUMIFS(B.kW,B.date,A3,B.time,">="&B3,B.time,"<="&C3)

gives 126.283. So, let's test the following:

=COUNT(B.kW)
=COUNT(B.time)
=COUNT(B.date)

What results do you get?

 

[EnergySaver]

10,080 for =COUNT(B.kW)
5,040 for =COUNT(B.time)
10,080 for =COUNT(B.date)

I am sure you are going to explain why B.time is only 5,040 especially when I look at Name Manager ... the "Refers To" column shows $*$3:$*$10082 for each named-range (which is 10,080 rows) ... so I am puzzled as to why B.time is only 5,040???

Here's some additional information about B.time ...

• The data in B.time is a calculated field
• the Fluke meter that was monitoring the facility had two fields for "time"
• Field #1 holds the actual time but always showed it as "AM"
• Field #2 shows the true or actual AM or PM

I used the following formula to convert from a time range of 00:00:00-to-12:59:59 to a time range of 00:00:00-to-23:59:59 ...

=IF(D3="PM",IF(TEXT(C3,"[hh]:mm:ss")<"12:00:00",TEXT(C3,"[hh]:mm:ss")+TIME(12,0,0),TEXT(C3,"[hh]:mm:ss")),IF(TEXT(C3,"[hh]:mm:ss")-TIME(12,0,0)<0,TEXT(C3,"[hh]:mm:ss"),TEXT(C3,"[hh]:mm:ss")-TIME(12,0,0)))

This formula accurately changes a 01 PM time to a 13 time and it also handles the tricky part of making 12-AM to be 00 and 12-PM to be 12!

Many thanks for your help so far ... [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 22222]I look forward to hearing from you soon![/COLOR]

 

[EnergySaver]

This problem is SOLVED!

Aladin,

My hat and appreciation goes out to you ... you got me pointed in the right direction and it became a data problem versus a SUMIFS formula problem!

I determined that half of the time entries in B.time were not subject to a calculation ... so, I added a "* 1" into two spots of my formula and voila, the count for B.time went to 10,080 and my SUMIFS formula worked.

Here's my B.time corrected formula or where I added the multiply by 1 tweak ("*1") ...

=IF(D3="PM",IF(TEXT(C3,"[hh]:mm:ss")<"12:00:00",TEXT(C3,"[hh]:mm:ss")+TIME(12,0,0),TEXT(C3,"[hh]:mm:ss")*1),IF(TEXT(C3,"[hh]:mm:ss")-TIME(12,0,0)<0,TEXT(C3,"[hh]:mm:ss")*1,TEXT(C3,"[hh]:mm:ss")-TIME(12,0,0)))

... and here is my successful SUMIFS formula ...

=SUMIFS(B.kW,B.Date,B3,B.Time,">="&C3,B.Time,"<="&D3)


I trust this detail will help others solve their problem much faster than I was able to solve it!!!

 

[Aladin Akyurek]

You are welcome.