Count occurrences which are distinct based on 2 fields?

[Ber93]

Hi, good afternoon.

I'm facing a problem that I don't really know how to solve. I have a file with thousands of rows including Job IDs and agent names. The same job ID can appear many times, sometimes under the same agent, sometimes under a different agent. I need to count the number of times that each Job ID occurs for each agent. In the screenshot is an example of how I need it to look. 

Job ID 1 gets counted as 2 for John (because it occurs 2 times under his name), and it gets counted as 1 for Sebastian (because it occurs only 1 time under his name). I hope this is clear. Any help is much appreciated

[#2 · Accepted solution]

@Ber93 

=IF(COUNTIFS($CX$2:CX2,CX2,$CY$2:CY2,CY2)=1,COUNTIFS($CX$2:$CX$11,CX2,$CY$2:$CY$11,CY2),"")

Is this what you are looking for?

[#3]

Yes, that works perfectly! Thank you

[#4]

@OliverScheurich A followup question if you don't mind.

The formula works in the example table I made, but when I applied it to my actual table, I had to extend the range, because obviously the formula would stop working after the 11th row. When I switched the range to cover the entire formula, it stops working altogether and gives very weird, high values.

In my actual ranges, A=CX, and CM=CY

This is how I applied it to my actual table: 

=IF(COUNTIFS($A$2:A2,A2,$CM$2:CM2,CM2)=1,COUNTIFS($A$2:$A:$A,A2,$CM$2:$CM:$CM,CM2),"")

[#5]

@Ber93 

=IF(COUNTIFS($A$2:A2,A2,$CM$2:CM2,CM2)=1,COUNTIFS($A:$A,A2,$CM:$CM,CM2),"")

You can try this formula which seems to work in my spreadsheet.

[#6]

For some reason it didn't work for me, I'm probably doing something wrong. However I fixed it by making the ranges CM90000000. Crude, but it should work

[#7]

@Ber93 

Your ranges $A$2:$A:$A and $CM$2:$CM:$CM are entire columns of over a million cells each.

I would prefer a Table that resizes to match the data.

= IF(
     NOT(COUNTIFS(JobID,[@JobID],Agent,[@Agent],Seq,"<"&[@Seq])),
     COUNTIFS(JobID,[@JobID],Agent,[@Agent]),
     ""
  )

[#8]

@Peter Bartholomew I tried it but it's giving me a syntax error. I think it's something around the Seq part. I added it like this:
=IF(NOT(COUNTIFS([Job Id (as a string)],[@[Job Id (as a string)]],[Agent name],[@[Agent name]],Seq,"<"&[@Seq])),COUNTIFS([Job Id (as a string)],[@[Job Id (as a string)]],[Agent name],[@[Agent name]]),"")

[#9]

@Ber93 

My fault I think.  I had both structured references and defined names that referred to them.  The structured references are more explicit but can make the formulae rather long.

=IF(
    NOT(COUNTIFS(
      [Job Id (as a string)],[@[Job Id (as a string)]],
      [Agent name],[@[Agent name]],
      [Seq],"<"&[@Seq])),
    COUNTIFS(
      [Job Id (as a string)],[@[Job Id (as a string)]],
      [Agent name],[@[Agent name]]),
    "")

Making [Seq] a structured reference to the helper column should sort it.  As an aside, if you had dates in ascending order, they could be used instead of inserting a sequence/serial number.

[#10]

Good one! I would never have thought of it