Sumproduct or Countif on a 2D matrix
<p>I'm working on data from a population of people with allergies. Each person has a unique ExceptionID, and each allergen has a unique AllergenID (451 in total).</p>

<p>I have a data table with 2 columns (ExceptionID and AllergenID), where each person's allergies are listed row by row. This means that the ExceptionID column has repeated values for people with multiple allergies, and the AllergenID column has repeated values for the different people who have that allergy.</p>

<p>I am trying to count how many times each pair of allergies is present in this population (e.g. Allergen#107 &amp; Allergen#108, Allergen#107 &amp; Allergen#109,etc). To keep it simple I've created a matrix of 451 rows X 451 columns, representing every pair (twice actually because A/B and B/A are equivalent).</p>

<p>I somehow need to use the row name (allergenID) to lookup the ExceptionID in my data table, and count the cases where that matches the ExceptionIDs from the column name (also AllergenID). I have no problem using Vlookup or Index/Match, but I'm struggling with the correct combination of a lookup and Sumproduct or Countif formula.</p>

<p>Any help is greatly appreciated!</p>

<p>Mike
PS I'm using Excel 2016 if that changes anything.</p>

<p>-=UPDATE=-
So the methods suggested by Dirk and MacroMarc both worked, though I couldn't apply the latter to my full data set (17,000+ rows) because it was taking a long time.</p>

<p>I've since decided to turn this into a VBA macro because we now want to see the counts of triplets instead of pairs.</p>