Search code examples
excelexcel-formulasumifs

SUMIFS Between Two Dates and Times in Excel

I am trying to get the summation of a column between two dates and two times, where the days are different.

The columns I have are:

Table 1 = Time1, Time2, Date1, Date2

Table 2 = Date, Time, Event

Ideally, this formula would give the total number of events between Date1/Time1 and Date2/Time2

I have tried the formula below, but am receiving the total number of events between those two times on both dates, instead of the number of events between the two.

Time Start  Time End    Date Start  Date End
23:54:00    0:04:00     9/27/2021   9/28/2021

=SUMIFS(Sheet1!$G:$G,Sheet1!$D:$D,">="&'ProductMadness-Postlogs Export'!U84,Sheet1!$D:$D,"<="&'ProductMadness-Postlogs Export'!V84,,Sheet1!$C:$C,">="&'ProductMadness-Postlogs Export'!Y84,Sheet1!$C:$C,"<="&'ProductMadness-Postlogs Export'!Z84)

*For context, Sheet1 has the event data, "ProductMadness - Postlogs" has the date and time data

Solution

  • If I understand this correctly the following formula should be what you are looking for.

    =SUMIFS(Sheet1!$G:$G,Sheet1!$D:$D,">"&'ProductMadness-Postlogs Export'!U84,Sheet1!$D:$D,"<"&'ProductMadness-Postlogs Export'!V84,Sheet1!$C:$C,">"&'ProductMadness-Postlogs Export'!Y84,Sheet1!$C:$C,"<"&'ProductMadness-Postlogs Export'!Z84)

    By excluding the equal to it only counts what is between. That being said this still may not do what you want depending on if the times are just time values. For example, 23:54:00 is greater than 0:04:00. Since you were receiving results in the past, I am assuming your time have the full date value and are just formatted as time.