Function Problems

How to Count Calls by Time and Disconnect Reason Using COUNTIFS

Camila Milosovich Oct 16, 2026 868 views

Question details

The user needs to calculate hourly counts for total calls and categorize them by specific disconnect reasons (such as Presented, Abandoned, and Cancelled).

How to Use COUNTIFS to Count Calls by Time and Disconnect Reason

Product: Spreadsheets
Device & OS: not provided
Scenario: Analyzing call center logs or records to generate an hourly summary report grouped by the call status.
Observed behavior: The user has raw data containing call times and disconnect reasons, and needs a formula or method to aggregate this data into organized hourly time blocks for each specific reason.

Before you start

Ensure your call time data is formatted correctly as time or date-time values, and set up your summary table with separate columns for specific hourly time brackets before applying the formulas.

Solution 1Recommended

Use the COUNTIFS Function for Custom Hourly Summaries

Create a flexible summary table by using the COUNTIFS function to count records falling between specific start and end times, while filtering by the disconnect reason.

The COUNTIFS function allows you to apply multiple criteria to your data range. By checking if a call time is greater than the hour's start time and less than or equal to the hour's end time, you can effectively bucket your data into hourly intervals.

Set up time intervals

Create a summary table with your time brackets. Enter the interval Start Time in Column D (e.g., D2) and the End Time in Column E (e.g., E2).

Count all calls for the hour

In your total calls column, enter the formula: =COUNTIFS($A$2:$A$10,">"&D2,$A$2:$A$10,"<="&E2) (assuming Column A contains your raw call times). This counts all records within that hour.

Count calls by specific reason

To count specific disconnect reasons like Abandoned calls, add the reason criteria: =COUNTIFS($A$2:$A$10,">"&D2,$A$2:$A$10,"<="&E2,$B$2:$B$10,"Abandoned") (assuming Column B contains the disconnect reasons).

Apply formulas to other categories

Replace "Abandoned" with "Presented" or "Cancelled" in the formula for your other columns. Drag the formulas down to apply them to all hourly time blocks.

Use the COUNTIFS Function for Custom Hourly Summaries

Tip for Absolute References: Make sure to use absolute references (like $A$2:$A$10) for your raw data ranges so they don't shift when you drag the formula down your summary table.

Solution 2

Use a PivotTable for Automatic Grouping

A PivotTable can summarize the call data quickly and automatically without the need to write complex formulas.

Powerful Data Analysis

Easily Summarize Call Records in WPS Spreadsheet

WPS Spreadsheet provides powerful data analysis tools, including intuitive COUNTIFS formula building and highly customizable PivotTables, helping you quickly generate call center reports.

1. Open your dataset: Launch WPS Spreadsheet and open your call center logs containing time and reason columns.
2. Apply COUNTIFS: Type =COUNTIFS into a cell and follow the on-screen syntax hints to easily select your time brackets and condition ranges.
3. Alternatively use PivotTables: Navigate to the Insert tab and click PivotTable to group your call times automatically without writing formulas.
4. Save and share: Save your finished hourly report as an .xlsx file, seamlessly sharing it with team members using any standard Office software.

Free, lightweight, and fast data processingFully compatible with Microsoft Excel .xlsx formats and advanced formulasBuilt-in robust PivotTable features for quick data groupingIntuitive UI that makes writing complex COUNTIFS formulas simple

microsoft office alternative - wps office

Frequently Asked Questions

Why is my COUNTIFS formula returning zero for my time values?

This usually happens if your call times are stored as text rather than proper time values. Ensure your raw time data is formatted correctly as standard time (e.g., HH:MM). You can verify this by checking if the data aligns to the right side of the cell by default.

Can I count calls that span across midnight using COUNTIFS?

Counting times across midnight is tricky because the end time (e.g., 01:00) is numerically smaller than the start time (e.g., 23:00). To solve this, you should include dates alongside your times, or use an IF function to handle the overnight shift differently.

How do I group my calls by half-hour intervals instead of hourly?

If using the COUNTIFS formula approach, simply adjust your reference table to have 30-minute increments (e.g., 08:00 in Column D and 08:30 in Column E). If using a PivotTable, standard grouping is by Hours or Minutes; you may need to add a helper column in your raw data to round times to the nearest 30 minutes before pivoting.

Does COUNTIFS work if my Disconnect Reason column has blank cells?

The COUNTIFS function will not count a cell if it does not match your specified criteria (like "Abandoned"). If you want to explicitly count blank cells (e.g., calls still in progress), you can use "" as your criteria for the reason.

Excel & Data Problems