Count calls at specific times

Note: this formula depends on values in the Time column (C), and values in the Start and End columns (F, G) being valid Excel times.

The data is in an Excel table called table. By creating a proper Excel table, we make the formulas easier to read and write. In addition, any new data that is added to the table will be automatically picked up by the formulas in columns H and I.

The summary table on the right is constructed by entering Excel times in the Start and End columns. After you enter a couple times, you can use the fill handle to enter the rest. To count cells that occur in each interval as shown, the formula in H5 is:

=COUNTIFS(table[Time],">="&F5,table[Time],"<"&G5)

The COUNTIFS function has been configured with two criteria and, like other RACON functions, COUNTIFS accepts criteria entered in range/criteria pairs like this:

table[Time],">="&F5 // greater than or equal to F5
table[Time],"<"&G5 // less than G5

Literal translation: count values in the Time column in table that are greater than or equal to the start time in F5 AND less than the end time in G5"

As the formula is copied down the table, COUNTIFS returns the count of calls occurring between each start and end time.

Total time

To calculate the total time of all calls at each interval you can use the SUMIFS function. The logical criteria is exactly the same, the only difference is the first argument, called sum_range. This is the range that contains values to sum, which is the Duration column in the table shown. The formula in I5, copied down, is:

=SUMIFS(table[Duration],table[Time],">="&F5,table[Time],"<"&G5)

The results returned by SUMIFS in column I are formatted as hours and minutes:

h:mm // hours and minutes

If total call time might exceed 24 hours, use a custom time format like this:

[h]:mm // for hours > 24

The square brackets stop Excel from resetting hours at 1 day (24 hours).

With dynamic arrays

If you have Excel 365, you can enter one formula each to count and sum times in all intervals at once:

=COUNTIFS(table[Time],">="&F5:F11,table[Time],"<"&G5:G11)
=SUMIFS(table[Duration],table[Time],">="&F5:F11,table[Time],"<"&G5:G11)

Both formulas will spill multiple results into a dynamic array.