Need to create a matrix that sums the number of paired observations.

Situation: I am attempting to create 3 matrices from a set of raw data. The current process is to create a series of intermediate matrices and then combine these to create the final 3. This is cumbersome and I wonder if there is a better way.

If someone can point out how to simplify one of the three matrices I can work out the other two.

The attached file RatingsComparisons.xlsx contains a small portion of what I need to accomplish.

Raw data: A trainer and several observers watch a video of technicians performing a series of tasks. The trainer and observers rate each technician on each task performed. The ratings will be either A-D or 1-4. This raw data is replicated 3x in B2:L61 in the attached spreadsheet.

From the raw data I need to build 3 matrices. Each matrix includes all possible pairings of all observers:
Observer 1 with Observer 2; Observer 1 with Observer 3, Observer 2 with Observer 3, etc. The final matrices are found in columns P:AE. The intermediate matrices are found in columns AG:HE.

Within each observer pair I need to count the number of instances in the raw data in which:
For matrix 1) The paired ratings from both observers match exactly the trainer'ratings. (P3:AE18)
For matrix 2) The paired ratings from both observers differ from the trainer's ratings (Note: the observers' ratings do not need to match each other). The pair is counted if ratings for both observer X and Y differ from the trainer's rating. (P24:AE39)
For matrix 3) The paired ratings from both observers match each other but do not match the trainer's ratings (this is a subset of #2 above). (P45:AE60)

To hopefully make more sense, I have color coded the first three matched pairs (GH & AR; GH & TH; GH & CS) in each of the matrices.

It doesn't matter if the matrices are lower left or upper right (or full). I just need the complete matrices.

Finally, if anyone is curious, this project is not about the technician's performance. This is about how closely each observer matches both the trainer and all other observers. The observers are the ones in training and are watching the videos. Observers who consistently differ from the trainer and other observers will repeat the training. The training will be modified in instances in which multiple observers' ratings collectively differ from the trainers' ratings.

Thanks in advance.

Thank you for your assistance. Please note that the three formulas you provided are identical, all creating the first matrix. Could you offer guidance on how to modify these to create the second and third matrices?

You are correct. My mistake was not properly copying the 2nd & 3rd formulas from your post above. The resulting paste was a duplicate of the first. I appreciate your pointing this out.