Multi filter in Excel Formula

Question

Here is what I am looking to do. I have a data list with Supervisor/Agent/Script. the formula I am trying to build pulls per supervisor and script to show how many agents under a supervisor used a specific script example below:

so in the example John had 2 agents that used S1 and 1 agent that used S2. I could write this in VBA easily but the ask is for a formula.

Thanks!

Answer 1

Use three formula:

In F1:

=TRANSPOSE(UNIQUE(C2:C13))

In E2:

=UNIQUE(A2:A13)

In F2:

=COUNT(UNIQUE(FILTER(MATCH($B$2:$B$14,$B$2:$B$14,0),($A$2:$A$14=$E2)*($C$2:$C$14=F$1),"a")))

and copy over and down.