COUNTIFS FORMULA HELP - How many calls were made from x to x per hour

Copper Contributor

I need to know how many calls were made from "Caller Number" (1053) to "Callee Number" (6400) each hour (00:00-00:59)

I use the formula =COUNTIFS($A$2:$A$9999, "1057", $B$2:$B$9999, "6400" to give me the total amount of calls.

Where A:A is the "Caller Number" B:B is the "Callee Number.

D:D is the "Strat Time" (1/01/2024 1:40:29 AM) final column of data required to be sorted from.


I can't add the hour component to to the Countifs to work out each hour group.

Caller NumberCallee NumberCaller ID NumberStart Time
10576400"Apt 805" <1057>1/01/2024 0:11

Any help please.

2 Replies
Create a pivot table with Start time as first row field and caller number and callee number as other row fields. Then group the time column by hour. Now you can filter the table on either callee or caller number as needed.
You can do a helper column to Equal the date time column. The highlight the new helper column, right click and select number format. Select Customize and you can enter HH:MM:SS. You will need to do a SUMIFS to total the time. Ideally you want call duration for a meaningful sum. You could divide call duration SUMIFS, by COUNTIFS to get an average.

Even adding date ranges is possible if you want a table of formulas to show, a sum, count or average for each member of staff or customer. For this month the two criteria would be ">31/01/2024" and "<01/03/2024" both with the same column to be stated as the range.