Creating a Clashing Grid - COUNTIFS and/or SUMPRODUCT Array Formula?

I wonder if anyone can advise? I am trying to create the following clashing grid to count the number of times two items appear together on the same row.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K
1		Apples	Bananas	Cherries	Damsons	Elderberries
2	Apples	4	4						Apples	Bananas	Elderberries
3	Bananas	4	4						Bananas	Apples	Cherries
4	Cherries			3					Elderberries	Cherries	Damsons
5	Damsons				2				Damsons	Bananas	Apples
6	Elderberries					2			Apples	Cherries	Bananas

Sheet: Sheet1

The table on the right has rows of choices: no choice can be repeated on any row, so each row will have three unique entries. In the clashing grid on the left I need to show the total of times paired items appear at their intersection point on the clashing grid. Here apples and bananas appear together in a row four times, so the number 4 should appear at their intersection on the clashing grid. What would be the formula for cell B3, please?

Try this in B2 (CSE):

Formula:

Please Login or Register  to view this content.

And drag to other cells.

Hi Ali,

This looks a lot like a problem I had a few years ago. I wanted to know how many times people were playing golf in the same threesome (on the same row). See if this formula doesn't make your heart stop. In B2 and then pull and drag..

=SUMPRODUCT((MMULT(($I$2:$K$6=B$1)+($I$2:$K$6=$A2),{1;1;1})=2)+0)

If you really need the zeros then do this in B2

=IF(SUMPRODUCT((MMULT(($I$2:$K$6=B$1)+($I$2:$K$6=$A2),{1;1;1})>=2)+0)>1,SUMPRODUCT((MMULT(($I$2:$K$6=B$1)+($I$2:$K$6=$A2),{1;1;1})>=2)+0),0)

Clashes Grid for Ali.xlsx

Note = if you had 4 columns then make the {1;1;1} a {1;1;1;1}.

I need to give credit to either DaddyLongLegs or 6StringJazzer for my answer above. I worked on my problem for hours and it gave me a huge headache. It took me a few hours of study to see why it worked.

If your values can only appear once in each row, you don't actually require the >0 part of zbor's formula. I came up with
=SUM(COUNTIF(OFFSET($I$2:$K$6,ROW($I$2:$K$6)-ROW($I$2:$K$2),0,1),B$1)*COUNTIF(OFFSET($I$2:$K$6,ROW($I$2:$K$6)-ROW($I$2:$K$2),0,1),$A3))
though I must say I do like Marvin's answer better!

The original post stipulated that "no choice can be repeated on any row, so each row will have three unique entries"

Thank you ALL very much! Marvin's elegant and quite short solution is the one I'm going with. I knew one of you would work it out.