# SUMIFS Date-and-Time Range spanning multiple days

#### EnergySaver

##### New Member
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.

### 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
Maybe this...

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

M.

#### EnergySaver

##### New Member
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

##### MrExcel MVP
You are very welcome. Glad to help

M.

Replies
5
Views
65
Replies
3
Views
130
Replies
4
Views
181
Replies
5
Views
339
Replies
2
Views
146

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.

### Which adblocker are you using?

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

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