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.
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.