How to Count Unique number of callers (by Agent) but if caller exists on another table under another Agent name then dont count this as his caller?

Question

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:

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.

We are trying to count how many incoming callers were answered by agents. ( from a Calls data Sheet with another confirmation check on another job data sheet).

Conditions:

1. UNIQUELY Count (Without Duplicated Caller Numbers range: Calls!H:H) by Agent Name 'Live Report'!B3 = (Calls!E:E)

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 calls uniquely for each agent, but it counts from a Calls sheet that has been removed duplicates from using a different formula upfront on another sheet:

=COUNTIFS(RemovedDuplicatedCalls!E:E,'Live Report'!B3)

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 Agent submitted it.

Formula needs to Uniquely count how many times an agent had a call from a Caller(that wasn't submitted by another) and output on Live Report Sheet starting at D3 ('Live Report'!D3) column D (Unique Calls): Final Live Report Sheet:

SumProduct? Maybe combined with index/match or countifs? im exhausted from trying :O

Help is much appreciated

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

Updating the question more clearly: The output we are trying to achieve is:

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)

& 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 :)

I tried this method:

=COUNTIFS(Calls!E:E,'Live Report'!B3)-SUMPRODUCT(('Job Data (Closed)'!D:D="'Live Report'!B3")*('Job Data (Closed)'!M:M="'Calls!H:H"))

thinking to count all calls and then reduce callers that exist under a different agent name - it does not work since it doesn't know the row on the first countifs when it counts the Sumproduct, There's a Range Calls!H:H instead of one cell to compare to on the criteria2 of the SumPRoduct...

Answer 1

Fun with Array Formula, via SUMPRODUCT!

Count Unique entries:

=SUMPRODUCT(1/COUNTIF(Target_Range, Target_Range))

For every entry in the list, it adds 1, divided by the number of times that value appears in the list. This means, if the value "Hello World" appears 3 times in the list, then it adds 1/3 for each "Hello World" - which adds up to 1 for that Unique Value

Count Unique entries, Excluding from another list:

=SUMPRODUCT(--(COUNTIF(Exclude_Range, Target_Range)<1)/COUNTIF(Target_Range, Target_Range))

The only change here is that we check if the entry appears in our list of entries to exclude, and convert that to 1 or 0. Since 0 divided by anything is still 0, it means we do not add any count for that Value