Formula for co-occurrence with counting and percentage?

Welcome to the forum.

Please explain the results - for example, the 5 for shirst and pants.

No. of rows in which they both occur. It'll probably be a horrible MMULT... MMULT /or I can do... MMULT /and is more tricky.

How about this (not that horrific)?

=IF(I$1=$H2,0,SUMPRODUCT((MMULT(($B$2:$D$8=I$1)+($B$2:$D$8=$H2),{1;1;1})=2)+0))

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D	E	F	G	H	I	J	K	L
1		PRODUCT #1	PRODUCT #2	PRODUCT #3	COLOR				SHIRT	PANTS	COAT	JACKET
2	DAY1	SHIRT	PANTS	JACKET	RED			SHIRT	0	5	3	2
3	DAY2	PANTS	JACKET	COAT	YELLOW			PANTS	5	0	5	4
4	DAY3	COAT	PANTS	SHIRT	BLUE			COAT	3	5	0	2
5	DAY4	JACKET	COAT	PANTS	BLUE			JACKET	2	4	2	0
6	DAY5	SHIRT	PANTS	JACKET	RED
7	DAY6	PANTS	SHIRT	COAT	YELLOW
8	DAY7	SHIRT	COAT	PANTS	BLUE

Sheet: Sheet1

Better than mine!! Even though I did add on a few "extras"


=IF(COLUMNS($I2:I2)>ROWS(I$2:I2),"",IF($H2=I$1,0,SUM((MMULT(--($B$2:$D$8=$H2),TRANSPOSE(COLUMN($B$2:$D$8)))>0)*(MMULT(--($B$2:$D$8=I$1),TRANSPOSE(COLUMN($B$2:$D$8)))>0))))

PS. There was a mistake in your expected answers.

Yes, the 2 for coat should have been a 3.

So, a best-of-both would read:

=IF(COLUMNS($I2:I2)>ROWS(I$2:I2),"",IF(I$1=$H2,0,SUMPRODUCT((MMULT(($B$2:$D$8=I$1)+($B$2:$D$8=$H2),{1;1;1})=2)+0)))

It will when you drag copy it across and down. See attached.

In your version of Excel, this is probably an array formula.

So, please refer to the file. If the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Can the same product occur more than once in each row?

Eg can shirt occur twice in row 4? If so, does it count as 1 or as 2 rows????

Ali's formula will calculate it as 2 occurrences... mine (Post 5) as ONE.

Some comment from the OP at this point would be helpful - are either of us on the right track here?

We cannot diagnose an invisible problem. We need to see a sheet. CTRL SHIFT enter

Are you extending the ranges appropriately? Are there just three product columns or more? If more, how many?

So SIX product columns?

Try this:

=IF(I$1=$H2,0,SUMPRODUCT((MMULT(($B$2:$G$2000=I$1)+($B$2:$G$2000=$H2),{1;1;1;1;1;1})=2)+0))

Extend the ranges further, if required.

Use this:

=IF(COLUMNS($I2:I2)>ROWS(I$2:I2),"",IF(I$1=$H2,0,SUMPRODUCT((MMULT(($B$2:$D$8=I$1)+($B$2:$D$8=$H2),TRANSPOSE(COLUMN($B$2:$D$8))^0)=2)+0)))

with the ranges changed appropriately.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.