Help me simplify this countifs() formula to be able to expand it further

[RedMatrix]

Hello.

I have data in the first 5 columns, and 200+ rows, with no duplicates in each row. I want to see how many times certain values appear together in the same row, regardless of their column.

For example:
03 04 05 07 09
10 23 05 09 44
07 08 23 70 12
23 24 25 07 05
and so on (added zeroes for spacing)

Next to it i have headers from 2 through 70, and row headers from 1 through 70.

In that top left cell, I have the formula below, and copied to the entire matrix, and then I go back and delete the reverse duplicates. (2 & 5 is the same as 5 & 2)

=SUM(
COUNTIFS($A:$A,$G2,$B:$B,H$1), COUNTIFS($A:$A,$G2,$C:$C,H$1), COUNTIFS($A:$A,$G2,$D:$D,H$1), COUNTIFS($A:$A,$G2,$E:$E,H$1), COUNTIFS($B:$B,$G2,$A:$A,H$1), 
COUNTIFS($B:$B,$G2,$C:$C,H$1), COUNTIFS($B:$B,$G2,$D:$D,H$1), COUNTIFS($B:$B,$G2,$E:$E,H$1), COUNTIFS($C:$C,$G2,$A:$A,H$1), COUNTIFS($C:$C,$G2,$B:$B,H$1), 
COUNTIFS($C:$C,$G2,$D:$D,H$1), COUNTIFS($C:$C,$G2,$E:$E,H$1), COUNTIFS($D:$D,$G2,$A:$A,H$1), COUNTIFS($D:$D,$G2,$B:$B,H$1), COUNTIFS($D:$D,$G2,$C:$C,H$1), 
COUNTIFS($D:$D,$G2,$E:$E,H$1), COUNTIFS($E:$E,$G2,$A:$A,H$1), COUNTIFS($E:$E,$G2,$B:$B,H$1), COUNTIFS($E:$E,$G2,$C:$C,H$1), COUNTIFS($E:$E,$G2,$D:$D,H$1)
)

The number output tells me how many times the row and header values appear in the data, together in the same row, no matter the column they appear on.

So, all of these would be a match and be counted:

1-2-x-x-x, x-1-2-x-x, x-x-1-2-x, x-x-x-1-2, 2-x-x-x-1
1-x-2-x-x, x-1-x-2-x, x-x-1-x-2, 2-x-x-1-x, x-2-x-x-1
1-x-x-2-x, x-1-x-x-2, 2-x-1-x-x, x-2-x-1-x, x-x-2-x-1
1-x-x-x-2, 2-1-x-x-x, x-2-1-x-x, x-x-2-1-x, x-x-x-2-1

Then I made another section, to see how many times the number on the left, appears with two sequential numbers from 2/3, 3/4, 4/5, etc.

But this time, the countifs() code was horrendously long!

=SUM(
AND(COUNTIFS($B:$B,$H2,$C:$C,I$1), COUNTIFS($B:$B,$H2,$C:$C,I$1,$D:$D,j$1)), 
AND(COUNTIFS($B:$B,$H2,$C:$C,I$1), COUNTIFS($B:$B,$H2,$C:$C,I$1,$E:$E,j$1)),
AND(COUNTIFS($B:$B,$H2,$C:$C,I$1), COUNTIFS($B:$B,$H2,$C:$C,I$1,$F:$F,j$1)),
AND(COUNTIFS($B:$B,$H2,$d:$d,I$1), COUNTIFS($B:$B,$H2,$d:$d,I$1,$C:$C,J$1)),
AND(COUNTIFS($B:$B,$H2,$d:$d,I$1), COUNTIFS($B:$B,$H2,$d:$d,I$1,$E:$E,J$1)),
AND(COUNTIFS($B:$B,$H2,$d:$d,I$1), COUNTIFS($B:$B,$H2,$d:$d,I$1,$F:$F,J$1)),
AND(COUNTIFS($B:$B,$H2,$e:$e,I$1), COUNTIFS($B:$B,$H2,$e:$e,I$1,$C:$C,J$1)),
AND(COUNTIFS($B:$B,$H2,$e:$e,I$1), COUNTIFS($B:$B,$H2,$e:$e,I$1,$D:$D,j$1)),
AND(COUNTIFS($B:$B,$H2,$e:$e,I$1), COUNTIFS($B:$B,$H2,$e:$e,I$1,$F:$F,j$1)),
AND(COUNTIFS($B:$B,$H2,$f:$f,I$1), COUNTIFS($B:$B,$H2,$f:$f,I$1,$C:$C,J$1)),
AND(COUNTIFS($B:$B,$H2,$f:$f,I$1), COUNTIFS($B:$B,$H2,$f:$f,I$1,$D:$D,j$1)),
AND(COUNTIFS($B:$B,$H2,$f:$f,I$1), COUNTIFS($B:$B,$H2,$f:$f,I$1,$E:$E,J$1)),

AND(COUNTIFS($C:$C,$H2,$d:$d,I$1), COUNTIFS($C:$C,$H2,$d:$d,I$1,$B:$B,j$1)),
AND(COUNTIFS($C:$C,$H2,$d:$d,I$1), COUNTIFS($C:$C,$H2,$d:$d,I$1,$E:$E,j$1)),
AND(COUNTIFS($C:$C,$H2,$d:$d,I$1), COUNTIFS($C:$C,$H2,$d:$d,I$1,$F:$F,j$1)),
AND(COUNTIFS($C:$C,$H2,$e:$e,I$1), COUNTIFS($C:$C,$H2,$e:$e,I$1,$B:$B,j$1)),
AND(COUNTIFS($C:$C,$H2,$e:$e,I$1), COUNTIFS($C:$C,$H2,$e:$e,I$1,$D:$D,j$1)),
AND(COUNTIFS($C:$C,$H2,$e:$e,I$1), COUNTIFS($C:$C,$H2,$e:$e,I$1,$F:$F,j$1)),
AND(COUNTIFS($C:$C,$H2,$f:$f,I$1), COUNTIFS($C:$C,$H2,$f:$f,I$1,$B:$B,j$1)),
AND(COUNTIFS($C:$C,$H2,$f:$f,I$1), COUNTIFS($C:$C,$H2,$f:$f,I$1,$D:$D,j$1)),
AND(COUNTIFS($C:$C,$H2,$f:$f,I$1), COUNTIFS($C:$C,$H2,$f:$f,I$1,$E:$E,j$1)),
AND(COUNTIFS($C:$C,$H2,$B:$B,I$1), COUNTIFS($C:$C,$H2,$B:$B,I$1,$D:$D,j$1)),
AND(COUNTIFS($C:$C,$H2,$B:$B,I$1), COUNTIFS($C:$C,$H2,$B:$B,I$1,$E:$E,j$1)),
AND(COUNTIFS($C:$C,$H2,$B:$B,I$1), COUNTIFS($C:$C,$H2,$B:$B,I$1,$F:$F,j$1)),

AND(COUNTIFS($d:$d,$H2,$B:$B,I$1), COUNTIFS($d:$d,$H2,$B:$B,I$1,$B:$B,j$1)),
AND(COUNTIFS($d:$d,$H2,$B:$B,I$1), COUNTIFS($d:$d,$H2,$B:$B,I$1,$C:$C,j$1)),
AND(COUNTIFS($d:$d,$H2,$B:$B,I$1), COUNTIFS($d:$d,$H2,$B:$B,I$1,$F:$F,j$1)),
AND(COUNTIFS($d:$d,$H2,$C:$C,I$1), COUNTIFS($d:$d,$H2,$C:$C,I$1,$B:$B,j$1)),
AND(COUNTIFS($d:$d,$H2,$C:$C,I$1), COUNTIFS($d:$d,$H2,$C:$C,I$1,$C:$C,j$1)),
AND(COUNTIFS($d:$d,$H2,$C:$C,I$1), COUNTIFS($d:$d,$H2,$C:$C,I$1,$E:$E,j$1)),
AND(COUNTIFS($d:$d,$H2,$e:$e,I$1), COUNTIFS($d:$d,$H2,$e:$e,I$1,$C:$C,j$1)),
AND(COUNTIFS($d:$d,$H2,$e:$e,I$1), COUNTIFS($d:$d,$H2,$e:$e,I$1,$E:$E,j$1)),
AND(COUNTIFS($d:$d,$H2,$e:$e,I$1), COUNTIFS($d:$d,$H2,$e:$e,I$1,$F:$F,j$1)),
AND(COUNTIFS($d:$d,$H2,$f:$f,I$1), COUNTIFS($d:$d,$H2,$f:$f,I$1,$B:$B,j$1)),
AND(COUNTIFS($d:$d,$H2,$f:$f,I$1), COUNTIFS($d:$d,$H2,$f:$f,I$1,$E:$E,j$1)),
AND(COUNTIFS($d:$d,$H2,$f:$f,I$1), COUNTIFS($d:$d,$H2,$f:$f,I$1,$F:$F,j$1)),

AND(COUNTIFS($e:$e,$H2,$B:$B,I$1), COUNTIFS($e:$e,$H2,$B:$B,I$1,$B:$B,j$1)),
AND(COUNTIFS($e:$e,$H2,$B:$B,I$1), COUNTIFS($e:$e,$H2,$B:$B,I$1,$C:$C,j$1)),
AND(COUNTIFS($e:$e,$H2,$B:$B,I$1), COUNTIFS($e:$e,$H2,$B:$B,I$1,$D:$D,j$1)),
AND(COUNTIFS($e:$e,$H2,$C:$C,I$1), COUNTIFS($e:$e,$H2,$C:$C,I$1,$C:$C,j$1)),
AND(COUNTIFS($e:$e,$H2,$C:$C,I$1), COUNTIFS($e:$e,$H2,$C:$C,I$1,$D:$D,j$1)),
AND(COUNTIFS($e:$e,$H2,$C:$C,I$1), COUNTIFS($e:$e,$H2,$C:$C,I$1,$F:$F,j$1)),
AND(COUNTIFS($e:$e,$H2,$d:$d,I$1), COUNTIFS($e:$e,$H2,$d:$d,I$1,$B:$B,j$1)),
AND(COUNTIFS($e:$e,$H2,$d:$d,I$1), COUNTIFS($e:$e,$H2,$d:$d,I$1,$D:$D,j$1)),
AND(COUNTIFS($e:$e,$H2,$d:$d,I$1), COUNTIFS($e:$e,$H2,$d:$d,I$1,$F:$F,j$1)),
AND(COUNTIFS($e:$e,$H2,$f:$f,I$1), COUNTIFS($e:$e,$H2,$f:$f,I$1,$B:$B,j$1)),
AND(COUNTIFS($e:$e,$H2,$f:$f,I$1), COUNTIFS($e:$e,$H2,$f:$f,I$1,$C:$C,j$1)),
AND(COUNTIFS($e:$e,$H2,$f:$f,I$1), COUNTIFS($e:$e,$H2,$f:$f,I$1,$F:$F,j$1)),
 
AND(COUNTIFS($f:$f,$H2,$B:$B,I$1), COUNTIFS($f:$f,$H2,$B:$B,I$1,$C:$C,j$1)),
AND(COUNTIFS($f:$f,$H2,$B:$B,I$1), COUNTIFS($f:$f,$H2,$B:$B,I$1,$D:$D,j$1)),
AND(COUNTIFS($f:$f,$H2,$B:$B,I$1), COUNTIFS($f:$f,$H2,$B:$B,I$1,$E:$E,j$1)),
AND(COUNTIFS($f:$f,$H2,$C:$C,I$1), COUNTIFS($f:$f,$H2,$C:$C,I$1,$B:$B,j$1)),
AND(COUNTIFS($f:$f,$H2,$C:$C,I$1), COUNTIFS($f:$f,$H2,$C:$C,I$1,$D:$D,j$1)),
AND(COUNTIFS($f:$f,$H2,$C:$C,I$1), COUNTIFS($f:$f,$H2,$C:$C,I$1,$E:$E,j$1)),
AND(COUNTIFS($f:$f,$H2,$d:$d,I$1), COUNTIFS($f:$f,$H2,$d:$d,I$1,$B:$B,j$1)),
AND(COUNTIFS($f:$f,$H2,$d:$d,I$1), COUNTIFS($f:$f,$H2,$d:$d,I$1,$C:$C,j$1)),
AND(COUNTIFS($f:$f,$H2,$d:$d,I$1), COUNTIFS($f:$f,$H2,$d:$d,I$1,$E:$E,j$1)),
AND(COUNTIFS($f:$f,$H2,$e:$e,I$1), COUNTIFS($f:$f,$H2,$e:$e,I$1,$B:$B,j$1)),
AND(COUNTIFS($f:$f,$H2,$e:$e,I$1), COUNTIFS($f:$f,$H2,$e:$e,I$1,$C:$C,j$1)),
AND(COUNTIFS($f:$f,$H2,$e:$e,I$1), COUNTIFS($f:$f,$H2,$e:$e,I$1,$D:$D,j$1)),
)

Now I want to count how many times the number came out with 2/4, 3/5, 4/6, 5/7, etc. Then again with 2/5, 3/6, 4/7, 5/8, etc, etc, etc.
Eventually, I want to expand this to 3 consecutive numbers (2/3/4), and then skip a number (2/4/5), and then skip 2 numbers (2/5/6) etc ad nauseam.

Is there an easier way to do this?

 

[Automatrix]

I combined the five columns into a single column (G), and added a § between them as a delimiter. Then you count how many rows contain both §04 and §05, for example.

Book1
	A	B	C	D	E	F	G
1	03	04	05	07	09		§03§04§05§07§09
2	10	23	05	09	44		§10§23§05§09§44
3	07	08	23	70	12		§07§08§23§70§12
4	23	24	25	07	05		§23§24§25§07§05
5
6		01	02	03	04	05
7	01		0	0	0	0
8	02			0	0	0
9	03				1	1
10	04					1
Sheet1

Cell Formulas
Range	Formula
G1:G4	G1	="§"&A1&"§"&B1&"§"&C1&"§"&D1&"§"&E1
C7,F7:F10,E7:E9,D7:D8	C7	=COUNTIFS($G$1:$G$4,"*§"&$A7&"*",$G$1:$G$4,"*§"&C$6&"*")

 

[RedMatrix]

Hi Automatrix.

I don't understand your code. It's not working with the real data that I have. I modified it to allow me to put the grid on the right of the combined column, but I am getting big counts that don't seem right.

I know there is exactly only one time 1 and 2 came out in the same draw, not 168 times.

 

[RedMatrix]

I found the problem in your solution!! All I had to do was add the "§" to the end of the concatenation, and then search with it at the end of the criteria:

=COUNTIFS($F:$F,"*§"& $G2 &"§*",    $F:$F,"*§"& H$1 &"§*")

I think the problem was when looking at the 2nd criteria, it was only looking for numbers at the end since it didn't have a trailing "§*". I don't understand why, since the wildcard IS present. Oh well, thanks for your help.

 

[Automatrix]

I found the problem in your solution!! All I had to do was add the "§" to the end of the concatenation, and then search with it at the end of the criteria:

=COUNTIFS($F:$F,"*§"& $G2 &"§*",    $F:$F,"*§"& H$1 &"§*")

I think the problem was when looking at the 2nd criteria, it was only looking for numbers at the end since it didn't have a trailing "§*". I don't understand why, since the wildcard IS present. Oh well, thanks for your help.

View attachment 79569

My solution needed you to have 1 as §01 rather than §1, but I guess that using the trailing § works as well.