I have a dataset that has in it collections of 3 attributes from a wider pool of 16. I want to build a heat map that shows which attributes are more or less often connected to one another.

Currently my data is in the format (note this is a sample from a larger set of 70 rows):

I would like to be able to automatically populate the following table:

Is there a way that I can automate this process in excel, instead of manually counting each time two attributes are mentioned in the same set of three? I've seen some examples using SUMPRODUCT, but these examples seem to required each column to have a specific type of data in, whereas in my example all 3 columns could contain any of the attributes.

CodePudding user response：

As you have Excel 365, you can use a

Assumes an attribute can't occur more than once in a row.

As noted