SUMIFS Date-and-Time Range spanning multiple days

EnergySaver

New Member
Joined
Mar 4, 2018
Messages
5
All ... I could not find any posts explaining how to use SUMIFS spanning multiple days ... so I thought I would reach out and ask for help.

I have a SUMIFS formula working perfectly that will sum up all the values (kWh's) for a specific date within a start-time and an end-time ... here is the formula ...

=SUMIFS(B.kWh,B.Date,B9,B.Time,">="&C9,B.Time,"<="&E9); where ...
  • B.kWh is a named cell range for all the kWh values (it's in a separate tab in the same workbook)
  • B.Date is a named cell range for all the date values in the same tab as B.kWh
  • B9 is the date such as June 25, 2018 (it's a variable that I allow the user to change)
  • B.Time is a named cell range for all the time values in the same tab as B.kWh and B.Date
  • C9 is the start-time value entered by the user
  • E9 is the end-time value entered by the user

My problem ... I found out that second shift for my client spans two physical days (it starts at 16:30 on June 25 and ends at 03:00 on June 26) ...

So I thought all I would have to do is add a ">=" and "<=" as criteria for B.Date and I would be in business ... but, alas - I am not getting expected results ...

I want SUMIFS to total all kWh (in B.kWh) when an entry in the tab is greater-than-or-equal to a specific Start-Date-and-Time (supplied by the user) and at the same time is less-than-or-equal to a specific End-Date-and-Time.

Here are the constants for the following SUMIFS that I have tried to use to solve my problem ...
  • B4 = 6/11/2018
  • C4 = 23:59:00
  • D4 = 6/12/2018
  • E4 = 00:00:59
  • Total kWh should be 0.041


=SUMIFS(B.kWh,B.Date,">="&B4,B.Time,">="&C4,B.Date,"<="&D4,B.Time,"<="&E4) ... Returned 0 kWh

=SUMIFS(B.kWh,B.Date,">="&B9,B.Time,">="&C9,B.Date,"<="&D9,B.Time,"<="&E9) ... Returned 0 kWh
  • my thought here was to have the from-time right after the from-date
  • obviously it did not make a difference!
  • If I "open up" the from and to range I get a result BUT it was not the correct number

=SUMIFS(B.kWh,B.Date,">="&B9,B.Time,">="&C9)+SUMIFS(B.kWh,B.Date,"<="&D9,B.Time,"<="&E9)
  • this formula calculated the correct kWh (0.041)
  • when I added another minute to the To-Time (0:01:49) - it DID NOT WORK - came back with too high of a kWh number (58.388)
  • I don't think this approach is the answer because I truly need to meet 4 criteria BEFORE adding the value to my total kWh
    • From Date (B*), and
    • From Time (C*), and
    • To Date (D*), and
    • To Time (E*)

I look forward to learning how others would solve this problem.

Take care.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

EnergySaver

New Member
Joined
Mar 4, 2018
Messages
5
Marcello,

I just tried your suggestion and It WORKS!

I really appreciate you taking your time to help me out … your suggestion was elegantly simple and I could understand what was happening when I looked at it … it was able to handle a small date-and-time range (from 11-Jun 23:59:00 to 12-Jun 0:00:59) as well as a large range.

Thank you very much.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,732
Messages
5,524,513
Members
409,583
Latest member
RedHelp

This Week's Hot Topics

Top