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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,496
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Maybe this...

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

M.
 

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,127,328
Messages
5,624,034
Members
416,007
Latest member
csf

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