Use COUNTIFS in WPS Spreadsheets to Count Calls by Hour
Question details
Count call records for a specified caller and callee during each one-hour time period.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The worksheet contains caller numbers, callee numbers, and call start date-times, and the user needs an hourly call count.
- Observed behavior
- The required result is one count per hourly interval, using complete date-time values so calls are assigned to the correct hour.
Make sure the Start Time column contains real spreadsheet date-time values, not text. Enter the beginning of each reporting hour in a separate column, including both the date and time.
Count calls in each one-hour interval with COUNTIFS
Use this method when you need a formula-based hourly count for a particular caller and callee.
COUNTIFS counts records whose Start Time is greater than or equal to an hour start and less than the next hour. Using a half-open interval prevents calls at exactly the next hour from being counted twice.
Place caller numbers in column A, callee numbers in column B, and complete call start date-times in column D. Adjust the column references if your data is stored elsewhere.
In column F, enter the start of each reporting hour, such as 1/1/2024 12:00 AM, then fill down in one-hour increments. Select column F and use Home > Number Format to display values as h:mm AM/PM if needed.
In the result cell beside F2, enter =COUNTIFS($A$2:$A$9999,1053,$B$2:$B$9999,6400,$D$2:$D$9999,">="&F2,$D$2:$D$9999,"<"&F2+TIME(1,0,0)). Replace 1053 and 6400 with the required caller and callee values.
Select the formula cell and drag the fill handle down alongside the hour-start values in column F to return one call count for every hour.
Group call start times by hour in a PivotTable
Use a PivotTable when you want an interactive hourly summary that can be filtered by caller and callee.
Create hourly call reports in WPS Spreadsheets
WPS Spreadsheets lets you use COUNTIFS with date-time criteria to count calls in each hour, then build a PivotTable when you need a filterable hourly summary by caller and callee.
- 1. Open the call log: Open the .xlsx or .xls call-record workbook in WPS Spreadsheets and verify that the Start Time column is formatted as a date and time.
- 2. Create hourly start values: Enter full hourly date-time values in a reporting column, beginning with the first hour you want to evaluate.
- 3. Apply COUNTIFS criteria: Enter a COUNTIFS formula using the caller, callee, Start Time >= hour start, and Start Time < hour start + TIME(1,0,0) criteria.
- 4. Build a PivotTable when needed: Choose Insert > PivotTable, add Start Time to Rows, group it by Hours, and filter the report by caller and callee.
Frequently Asked Questions
Why must the hourly value in F2 include a date as well as a time?
A time such as 12:00 AM repeats every day. Entering a full date-time, such as 1/1/2024 12:00 AM, ensures COUNTIFS counts records from the correct day and hour.
How do I count calls for different caller and callee numbers?
Replace 1053 and 6400 in the COUNTIFS formula with the required values, or place the caller and callee criteria in cells and reference those cells in the formula.
Why does the formula use less than the next hour instead of less than or equal to it?
Using "<"&F2+TIME(1,0,0) excludes a call exactly at the next hour. This prevents that call from appearing in both adjacent hourly counts.
Can a PivotTable show hourly counts across multiple days?
Yes. In the PivotTable, right-click a Start Time row label and choose Group. Select both Days and Hours so each date has its own hourly breakdown.
