I wanted to count unique numbers in a excel and combine it with a countifs function

Question

I have a data sheet with these specific columns (among others)

Date time (in one column) Campaign name (I work at a call center) in another Agent ID number in a third

What I am trying to do is pull how many agents worked in which campaign within a certain time-frame.

I can count total unique agents using =SUM(IF(FREQUENCY(Dials!D:D,Dials!D:D)>0,1)) and number of calls per hour etc using =COUNTIFS(Dials!A:A,">="&D3,Dials!A:A,"<="&F3,Dials!F:F,"5020")) where 5020 is the campaign ID and D3 is the start of the hour and F3 the end of the hour.

However if I try to combine them to get the number of agents who worked on that campaign in that hour I get a zero result...

Any ideas?

Answer 1

Could you simply use something like =COUNTIFS(B:B,B2,C:C,C2,D:D,D2), where B contains the Campaign ID, C contains the start time, and D contains the end time?

Based on the data below, the formula I stated would produce 2, as James and David both worked from 1-2 on campaign 5020.

You're not technically counting the Agents field this way, but it works as long as you only have each unique agent logged once per campaign per time period.

You should also be able to use the >= and <= that you used in your original formula, if your time field formatting requires it.

   Col A    Col B       Col C   Col D
 ------------------------------------
1| Caller   Campaign    Start   End Hour
2| James    5020        1       2
3| David    5020        1       2
4| James    5021        2       3
5| James    5020        2       3

Answer 2

I think the following will get you what you want (please check to make sure). The expression below would be in the first cell of the distinct count of agents that made calls in a specified hour on behalf of a particular campaign.

=SUM(
    IF(
       (CallTimes>=D5)*(CallTimes<E5)*(Campaigns=Campaign),
       1/COUNTIFS(CallTimes,">="&D5,CallTimes,"<"&E5,Campaigns,Campaign,
                  Agents,Agents),
       0
      )
    )

This formula assumes that the call log is laid out with three columns named CallTimes, Agents, and Campaigns. The particular campaign to look at is entered in the cell named Campaign. The list of hour start times begins in cell D5 and the list of end times begins in cell E5. The formula above is entered in cell G5 and copied down. It is an array formula and must be entered with the Control-Shift-Enter key combination.

Unformatted formula

=SUM(IF((CallTimes>=D5)*(CallTimes<E5)*(Campaigns=Campaign),1/COUNTIFS(CallTimes,">="&D5,CallTimes,"<"&E5,Campaigns,Campaign,Agents,Agents),0))