EnergySaver
New Member
- Joined
- Mar 4, 2018
- Messages
- 5
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:
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:
B.date | B.time | B.kW |
12/15/2017 | 04:52:18 | 126.283 |
12/15/2017 | 04:53:18 | 240.146 |
12/15/2017 | 04:54:18 | 428.409 |
<tbody style="border-collapse: collapse; width: auto;">
</tbody>
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:
B | C | D | E | |
2 | Date | From Time | To Time | Total kW |
3 | 12/15/2017 | 04:50:00 | 04:52:18 | SUMIFS formula = 0 |
<tbody style="border-collapse: collapse; width: auto;">
</tbody>
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
Any and all help will be gratefully appreciated ... Thank You.
P.S. I am running Excel 2016 on a Windows 10 OS