Counting Specific Values From Sumproduct(countif)

Question

I have been sulking around this place for a while and until now I have been able to find an answer to virtually any problem I have come across. I feel like I am on the right path for the answer I need, but I can't seem to figure out the right way to go about it.

Below I have a screenshot of a simplistic example of what I am wanting to do. In the example, I have 4 people, who picked their top 5 favorite colors (I am horrible at generating fake data like this, so please excuse the color choices haha). Ultimately, I want to know how many individuals matched all 5 colors (doesn't have to be in the same order), how many matched 4, 3, 2, etc. To the right of the data set is ideally how I would want my data to look (H2:M11).

I have approached this a number of ways, but haven't had much success. The two methods that seem to be closest are:

SEE FAILED IDEA 1) I wanted to write a formula that basically said "count the number of times that sumproduct(countif(Colors from Person 2,3, and 4 : Colors from Person 1)) is equal to 5 (in this example zero). I would replicate the formula for 4 matching colors, 3, 2, and so on. Then just sum and average.

The formula itself is flawed and returns as error. So, since this didn't pan out...

SEE FAILED IDEA 2) I thought about making a "heat map" style table where I could have Persons 1 through 4 on rows and columns then do something I know that worked like sumproduct(countif(B4:F4,B5:F5)) for Person 1 vs Person 2 (answer 3). Only difference is that I would have to use an index(match) to identify the proper row by using the column name (see example). Drag for all 16 (4x4) cells and then just have a countif(F19:I22,"5") to count the number of each. Unfortunately, the use of index(match) within the countif causes a #REF!

Simplistic Example Data

If either concept can actually be made to work, I could manipulate the rest. I know I could just do the heat map design and manually change the formulas for each column, but my real data is over 100 rows long. VBA alternatives are also welcome.

I apologize if my description wasn't very clear. If it was confusing, just let me know and I will try to be more clear. THANKS!

Answer 1

Going with your failed idea number 2. Use this array formula for Step 1:

=SUM(COUNTIF(INDEX($B:$F,MATCH(J$2,$A:$A,0),0),INDEX($B:$F,MATCH($I3,$A:$A,0),0)))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

Step 2:

=COUNTIFS($J3:$M3,P$2,$J$2:$M$2,"<>" & $O3)