SUMIFS Date-and-Time Range spanning multiple days

[EnergySaver]

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.

 

[Marcelo Branco]

Maybe this...

=SUMPRODUCT(B.kWh,--(B.Date+B.Time>=B4+C4),--(B.Date+B.Time<=D4+E4))

M.

 

[EnergySaver]

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.

 

[Marcelo Branco]

You are very welcome. Glad to help

M.