Count If Number Repeats in 5 Day Period

Hi All,

I have a list of phone numbers that call into my call centre. I am trying to identify which customers call in more than once in a 5 day period.

The basic criteria for a customer to be counted is:
Call Duration >= 00:00:30
Same phone number is used
Same customer calls in in a 5 day period.


I have attached a sample wordbook.

Originally Posted by noboffinme

Hi SalientAnimal,

You will need a 'Rolling' count of the phone number to = 5 days from the current date.

Have a look at this link;

http://www.mrexcel.com/forum/excel-q...12-months.html

It's along the lines of what you need, a SUMIF for the phone number couples with the rolling date parameters.

Extra happy to help anyone trying to get call queues down.

Thanks, going to take a look at this. hope I can get this working :-). Looks a bit complicated for my knowledge :P.

It seems as if I have my count working the way I want it to and I have also added an exclusion. The only problem I have now is including the 5 day cycle in this formula.

COLUMN D = PHONE NUMBER
COLUMN H = DURATION
COLUMN A = RUNCATED DATE TO EQUAL PERIOD
COLUMN B = ACTUAL CALL DATE

=IF(D2="Incoming Call",,IF(H2>="00:00:30",SUMPRODUCT(($A:$A=$J$1)*($D:$D=D2)),0))

I would assume that I would need to include the ACTUAL CALL DATE into my SUMPRODUCT formula somehow...