Trying to count agents unique number of callers but if caller exists on another table under another agent name - then dont count this as his caller

[alon005]

Trying to count agents unique number of callers but if caller exists on another table under another agent name - then dont count this as his caller, sound easy right? HELP!!!!


Calls Sheet: https://pasteboard.co/HXTl3Xo.png
Agent Name / Dispatch = (Calls!E:E)
Callers Number / Phone = (Calls!H:H)


Job Data Sheet: https://pasteboard.co/HXTljPg2.png
Agent Name / Dispatch = 'Job Data (Closed)'!D:D
Callers Number / Phone = 'Job Data (Closed)'!M:M


Final Live Report Sheet: https://pasteboard.co/HXTlpZk.png


We are trying to count how many incoming callers were answered by agents. ( from a Calls data Sheet).
Conditions
1. UNIQUELY Count (Without Duplicated Caller Numbers range: Calls!H:H)


AND


2. That those calls also belong to them - Condition:
That were not submitted by another agent: Two step check here at once like this:


Look for current customer number from current row (Calls!H:H) Look for it inside the new range in the new table of customer numbers 'Job Data (Closed)'!M:M sheet:
IF exists make sure not under a different agent(dispatch) name Like this:


See that the Dispatch name on the same row of that number on the job data sheet is the same as the original agent name on the calls sheet:
Check that 'Job Data (Closed)'!D:D (Dispatch name) on the same row of the 'Job Data (Closed)'!M:M (callers number) that was found existing in the jobs sheet equals to the original Agent Name from the calls sheet (Calls!E:E). If not, then do Not Count this callers number as a call for this agent.


We already have a CountIFS formula counting customer calls uniquely for each agent, but it counts from a Calls sheet that has been removed duplicates using a different formula upfront to another sheet:
That is wrong since we are removing callers who called several times to different agents, without checking if those callers belong to them by checking if they do not exist under a different agent name in the Job Data (Closed) Sheet.


If they do exist under a different agent name in the Job Data (Closed) sheet then that call does Not need to be counted for them since another submitted it.
Formula needs to Uniquely count how many times an agent had a call from a customer(that wasnt submitted by another) and output on column D (Unique Calls) Sheet: Live Report


SumProduct? Maybe combined with index/match or countifs? im exhausted from trying Freaking out here...


Help is much appreciated, Thanks you.
Al.

 

[alon005]

--------- UPDATE --------------

After rethinking about this I Think this explains it all better as what is the output I am looking for:

Count If AND all conditions occur:


1. Look for Agent 'Live Report'!D3 in: (Calls!E:E) and count rows by Unique callers number (Calls!H:H) (maybe we can use =SUMPRODUCT(1/COUNTIF(<wbr>Target_Range, Target_Range)) for this)


& make sure before counting above that this occur:


2. Take the relevant Caller's Number from same row on condition 1, for example: (Calls!H123) from range (Calls!H:H) and look for it in Table 2 callers Numbers range: 'Job Data (Closed)'!M:M


If found - for example on row 80, Look at the found row on the new table 2 'Job Data (Closed)', and Match the found call's Agent Name, for example: 'Job Data (Closed)'!D80 from column Range D 'Job Data (Closed)'!D:D to the Agent name from Table 1 from range (Calls!E:E) row 123.


If its different then do Not Count this call for Agent: 'Live Report'!D3

If its the same name then count the call as 1 for Agent: 'Live Report'!D3

If Name Not found then count the call as 1 for Agent: 'Live Report'!D3

This explanation actually helped me think of what we need, I hope this will help you figure out what we need to formulate here. Appreciated much!!
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">Count If AND all conditions occur:


1. Look for Agent 'Live Report'!D3 in: (Calls!E:E) and count rows by Unique callers number (Calls!H:H) (maybe we can use =SUMPRODUCT(1/COUNTIF(<wbr>Target_Range, Target_Range)) for this)


& make sure before counting above that this occur:


2. Take the relevant Caller's Number from same row on condition 1, for example: (Calls!H123) from range (Calls!H:H) and look for it in Table 2 callers Numbers range: 'Job Data (Closed)'!M:M


If found - for example on row 80, Look at the found row on the new table 2 'Job Data (Closed)', and Match the found call's Agent Name, for example: 'Job Data (Closed)'!D80 from column Range D 'Job Data (Closed)'!D:D to the Agent name from Table 1 from range (Calls!E:E) row 123.


If its different then do Not Count this call for Agent: 'Live Report'!D3

If its the same name then count the call as 1 for Agent: 'Live Report'!D3

If Name Not found then count the call as 1 for Agent: 'Live Report'!D3

This explanation actually helped me think of what we need, I hope this will help you figure out what we need to formulate here. Appreciated much!!
</body>

 

[alon005]

Example:

Calls Sheet:


Agent Name / Dispatch is in range: (Calls!E:E)


Callers Number / Phone is in range: (Calls!H:H)


E (Agent Name) H (Callers Number)


John 1111111


Kelly 3333333


John 3333333


Kelly 2222222


John 4444444


John 1111111


Job Data Sheet:


Agent Name / Dispatch is in range: 'Job Data (Closed)'!D:D


Callers Number / Phone is in range: 'Job Data (Closed)'!M:M


D (dispatch) M (phone)


Kelly 2222222


John 3333333


Final Live Report Sheet:


A (Agent) D (Unique Calls)


John 3


Kelly 1


John won the caller 3333333 to be counted But not only because we removed duplicates from the calls sheet, but since he submitted this caller and existed in the Job data sheet with that caller. Kelly also spoke with caller 3333333 but she did not submit him to the job data sheet. both answered this caller.