where table is an Excel Table holding call times as shown. 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: The COUNTIFS function has been configured with two criteria and, like other RACON functions, COUNTIFS accepts criteria entered in range/criteria pairs like this: 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: The results returned by SUMIFS in column I are formatted as hours and minutes: If total call time might exceed 24 hours, use a custom time format like this: 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: Both formulas will spill multiple results into a dynamic array.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
