Exceljet

Quick, clean, and to the point

Count calls at specific times

Excel formula: Count calls at specific times
Generic formula 
=COUNTIFS(times,">="&start,times,"<"&end)
Explanation 

To count calls at specific time intervals, you can use the COUNTIFS function. In the example shown, the formula in H5 is:

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

where "table" is an Excel Table holding call times as shown.

How this formula works

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.