Forum Discussion

Technabilities's avatar
Technabilities
Copper Contributor
Feb 06, 2024

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

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.

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    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.
  • JamesMartin001's avatar
    JamesMartin001
    Copper Contributor
    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.

Resources