calculate array

[keda duck]

This is a problem I encountered in my work. I want to use formula to solve this problem

Each cell is two numbers separated by character "/", and the number may contain Decimal separator.
The cells may be empty.
B3 and B2 are compared and assigned scores, C3 and C2 are compared and assigned scores...
Finally, the sum of the assigned scores for each row and the second row is calculated and written in column W for each row.

Scoring rules:
2: Two cells are the same and neither is empty
1: A certain number in cell B3 is the same as two numbers in cell B2
0: At least one of the two cells is empty, or the two numbers in cell B3 are different from the two numbers in cell B2

Taking the sixth row as an example.
B6 is empty, so its score is 0.
the first number of c6 is 9, and the second number of c4 is 13, these numbers are not existed in c2, so its score is 0.
The first number of d6 is 9, and the second number of b2 is 12, 12 is existed in b2 but 9 is not existed in, so its score is 1.
……
the first number of j6 is 8, and the second number of j6 is 11, 8 and 11 are both in j2, so its score is 2.
……
and so on.

When I calculate the score for each cell, I sum the scores for each row.

calculate.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	no.	data 1	data 2	data 3	data 4	data 5	data 6	data 7	data 8	data 9	data 10	data 11	data 12	data 13	data 14	data 15	data 16	data 17	data 18	data 19	data 20	data 21
2	1	16/16	8/8	11/12	9/9	25.2/30.2	12/15	10/11	30/30	8/11	16/17.3	14/14	11/15	14/14	10/11	18/27	17/24	16/20	9/9	12/21	11/12	24/24
3	2	15/17	11/12	12/12	11/13	19/28.2	13/15	11/12	30/31	8/8	13/18.3	13/14	15/17	12/13	14/14	17/23	19/23	16/20	7/7	15/17	10/11	22/22
4	3	15/16	10/11	8/10	11/13	22/28.2	15/16	7/11	30/32	8/11	14/15	12/15.2	11/15	10/14	11/14	19/23	24/25	15/19	6/9	15/15	12/12	21/22
5	4	17/17	9/10	8/10	10/11	20/27.2	12/14	10/11	29/31.2	8/8	11/14	13/15	13/18	11/11	10/14	18/20	17/22	14/17	7/9	15/15	8/11	22/23
6	5		9/13	9/12	11/11	25.2/26.2	13/13	11/12	29/30	8/11	12/15	13/15.2	16/16	12/13	10/12	19/19	16/20	14/18	6/9	15/16	9/14	24/24
7	6	15/16	10/12	8/8	12/13	19.2/24.2	15/16	11/11	29/32.2	8/8	12/13	13/13	15/17	14/15	11/14	21/23	23/24	14/19	9/9	14/20	11/12	23/25
8	7	15/16	8/11	8/11	12/13	22/24.2	15/16	11/11	31.2/32	8/8	13/14	13/15.2	15/16	10/15	11/12	18/23	22/24	17/19	7/9	14/15	10/12	22/23
9	8	14/18	9/11	12/13	9/14	25.2/28.2	16/16	11/12	32/32.2	8/9	15/16	13/14	17/17	10/12	10/14	19/21	16/23	14/15	7/8	13/14	12/12	24/25
10	9	15/17	8/11	8/11	11/11	22.2/28.2	14/15	10/12	30/31	8/8	17/17	13.2/14.2	11/15	11/14	11.3/12	18/19	20/23	17/18	7/9	13/20	12/12	24/25
11	10	15/17	8/8	8/13	11/12	19/28.2		10/10	29/30	8/8	15/17	13/14.2	11/11	11/12	10/11.3	18/20	19/20	17/18	9/9	13/14	12/12	23/25
12	11	17/18	9/11	11/11		22.2/28.2	15/15	12/12	29/31	8/10		13.2/14	15/15	13/14	11/12	19/21		14/17	7/9	14/20	11/12	21/24
13	12	14/16	10/11	9/12	9/11	19/26.2	13/14	10/13	29/31.2	8/8	15/15	13/14	15/16	11/14		18/19	23/24	14/16	9/9	13/16	10/10	24/25
14	13	15/16		12/13	9/11	19/27.2	13/14	11/12	30/31.2	8/8	15/15	13/14.2	15/16	11/14	10/12	18/19	19/23	14/18	7/9	13/13	10/12	23/25
15	14	15/15	10/11	12/13	10/10	19/27.2	13/14	11/12	29/31.2	8/10	15/15	13/14	11/15	11/13	12/12	18/19	19/23	14/16	9/9	13/16	10/12	23/24
16	15	15/16	10/12	10/11	9/10	28.2/29.2	13/14	11/12	29/29	8/9	13/15	13/13	11/17	13/15	10/11	21/22	19/23	16/19	6/7	14/18	11/12	23/24
17	16	16/18	9/12	8/11	11/12	19/21	13/15	12/13	32/33.2	11/11	11/14	13/14.2	11/15	13/16	10/11	17/18	19/20	18/19	7/8	16/23	10/12	23/24
18	17	15/15	8/9	8/13	11/12	23.2/28.2	13/13	11/13	30/30	9/11	14/17	14.2/14.2	15/16	13/14	10/11	18/19	24/24	14/18	9/10	13/13	10/12	19/20
19	18	16/17	8/11	11/11	12/12	20/25.2	13/16	9/12	30/31.2	10/11	12/15	12.2/15.2	15/16	12/14	10/10	18/20	19/20	16/18	7/7	13/14	12/13	20/23
20	19	14/15	9/11	9/13	9/10	19/30.2	13/14	12/13	31.2/33.2	8/10	15/15		11/16	13/14	10/12	19/19	19/24	14/17	7/9	13/16	10/12	24/25
21	20	15/16	8/10	12/12	10/11	26.2/27.2	13/13	10/11	29/30	8/8	13/15	13/14	11/15	11/11	10/12	18/18	23/23	16/18	9/9	13/16	10/12	23/24
22	21		10/13	8/10	12/12	19/30.2	14/15	12/13	29/30	8/8	15/15	15/16.2	11/16	12/13	11/14	18/19	19/23	16/19	9/9	15/16	10/12	22/23
Sheet1 (2)

 

[nemmi69]

Is this it?

Sub Score()
Dim Wb As Workbook
Dim Ws As Worksheet
Dim ValStr As String
Dim ValSep As String
Dim ValSepLoc As Integer
Dim Val1 As Single
Dim Val2 As Single
Dim Val1R2 As Single
Dim Val2R2 As Single
Dim Rowloop As Long
Dim Colloop As Long

'Scoring rules:
'2: Two cells are the same and neither is empty
'1: A certain number in cell Bn is the same as two numbers in cell B2
'0: At least one of the two cells is empty, or the two numbers in cell Bn are different from the two numbers in cell B2

Set Wb = ThisWorkbook
Set Ws = Wb.Worksheets("Sheet1")
ValSep = "/"
Val1 = 0
Val2 = 0
Val1R2 = 0
Val2R2 = 0

For Colloop = 2 To 22
    For Rowloop = 2 To 22
        ValStr = Ws.Cells(Rowloop, Colloop).Value
        ValSepLoc = InStr(1, ValStr, ValSep)
        If ValSepLoc > 1 Then ' cell is blank or no value left of separator
            Val1 = Left(ValStr, ValSepLoc - 1)
        Else
            Val1 = 0
            Ws.Cells(Rowloop + 24, Colloop).Value = 0
        End If
        If ValSepLoc < Len(ValStr) Then ' no value right of separator
            Val2 = Right(ValStr, Len(ValStr) - ValSepLoc)
        Else
            Val2 = 0
        End If
        If Rowloop = 2 Then
            Val1R2 = Val1
            Val2R2 = Val2
        Else
            If Val1 = Val1R2 And Val2 = Val2R2 Then
                Ws.Cells(Rowloop + 24, Colloop).Value = 2 'values are the same
            ElseIf (Val1 <> Val1R2 And Val2 = Val2R2) Or (Val1 = Val1R2 And Val2 <> Val2R2) Then
                Ws.Cells(Rowloop + 24, Colloop).Value = 1 '1 value are different
            Else
                Ws.Cells(Rowloop + 24, Colloop).Value = 0 'No match
            End If
        End If
    Next Rowloop
Next Colloop
End Sub

 

[keda duck]

Is this it?
View attachment 95099

Sub Score()
Dim Wb As Workbook
Dim Ws As Worksheet
Dim ValStr As String
Dim ValSep As String
Dim ValSepLoc As Integer
Dim Val1 As Single
Dim Val2 As Single
Dim Val1R2 As Single
Dim Val2R2 As Single
Dim Rowloop As Long
Dim Colloop As Long

'Scoring rules:
'2: Two cells are the same and neither is empty
'1: A certain number in cell Bn is the same as two numbers in cell B2
'0: At least one of the two cells is empty, or the two numbers in cell Bn are different from the two numbers in cell B2

Set Wb = ThisWorkbook
Set Ws = Wb.Worksheets("Sheet1")
ValSep = "/"
Val1 = 0
Val2 = 0
Val1R2 = 0
Val2R2 = 0

For Colloop = 2 To 22
    For Rowloop = 2 To 22
        ValStr = Ws.Cells(Rowloop, Colloop).Value
        ValSepLoc = InStr(1, ValStr, ValSep)
        If ValSepLoc > 1 Then ' cell is blank or no value left of separator
            Val1 = Left(ValStr, ValSepLoc - 1)
        Else
            Val1 = 0
            Ws.Cells(Rowloop + 24, Colloop).Value = 0
        End If
        If ValSepLoc < Len(ValStr) Then ' no value right of separator
            Val2 = Right(ValStr, Len(ValStr) - ValSepLoc)
        Else
            Val2 = 0
        End If
        If Rowloop = 2 Then
            Val1R2 = Val1
            Val2R2 = Val2
        Else
            If Val1 = Val1R2 And Val2 = Val2R2 Then
                Ws.Cells(Rowloop + 24, Colloop).Value = 2 'values are the same
            ElseIf (Val1 <> Val1R2 And Val2 = Val2R2) Or (Val1 = Val1R2 And Val2 <> Val2R2) Then
                Ws.Cells(Rowloop + 24, Colloop).Value = 1 '1 value are different
            Else
                Ws.Cells(Rowloop + 24, Colloop).Value = 0 'No match
            End If
        End If
    Next Rowloop
Next Colloop
End Sub

No, the results of this code are error. such as h27, the first number of h3 is 13, and the second number of h3 is 15, 15 is existed in h2 but 13 is not existed in, so h27 is 1.
If the results of this code can be summed for each row and output in column W, it would be even better.

 

[Fluff]

Removed as incorrect.

 

[keda duck]

How about

=SUM(MAP($B$2:$V$2,B3:V3,LAMBDA(a,b,SUM(--ISNUMBER(FIND("/"&TEXTSPLIT(b,"/",,1)&"/","/"&a&"/"))))))

Sorry, the results of this formula are error. Maybe I didn't explain it clearly.
Such as row3, the first and the second number of h3 is 12, 12 is existed in h2 but the first number of h2 is not existed in h3, so its score is 1.
The first and the second number of j3 is 8, 8 is existed in j2 but the second number of j2 is not existed in j3, so its score is 1.
The results of your formula are both 2 scores.

 

[Fluff]

Sorry, the results of this formula are error

Yes I realised that & removed the formula.

 

[Georgiboy]

I have been playing with this, does the answer column below give the correct answer for the data i have been using?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1	no.	data 1	data 2	data 3	data 4	data 5	data 6	data 7	data 8
2	1	16/16	25.2/30.2	30/30	14/14	18/27	17/24	16/20	24/24		16	16	25.2	30.2	30	30	14	14	18	27	17	24	16	20	24	24																	Answer
3	2	15/17	19/28.2	30/31	13/14	17/23	19/23	16/20	22/22		15	17	19	28.2	30	31	13	14	17	23	19	23	16	20	22	22		0	0	0	0	1	0	0	1	0	0	0	0	1	1	0	4
4	3	15/16	22/28.2	30/32	12/15.2	19/23	24/25	15/19	21/22		15	16	22	28.2	30	32	12	15.2	19	23	24	25	15	19	21	22		0	1	0	0	1	0	0	0	0	0	0	0	0	0	0	2
5	4	17/17	20/27.2	29/31.2	13/15	18/20	17/22	14/17	22/23		17	17	20	27.2	29	31.2	13	15	18	20	17	22	14	17	22	23		0	0	0	0	0	0	0	0	1	0	1	0	0	0	0	2
6	5	16/16	25.2/26.2	29/30	13/15.2	19/19	16/20	14/18	24/24		16	16	25.2	26.2	29	30	13	15.2	19	19	16	20	14	18	24	24		1	1	1	0	0	1	0	0	0	0	0	0	0	0	1	5
7	6	15/16	19.2/24.2	29/32.2	13/13	21/23	23/24	14/19	23/25		15	16	19.2	24.2	29	32.2	13	13	21	23	23	24	14	19	23	25		0	1	0	0	0	0	0	0	0	0	0	1	0	0	0	2
8	7	15/16	22/24.2	31.2/32	13/15.2	18/23	22/24	17/19	22/23		15	16	22	24.2	31.2	32	13	15.2	18	23	22	24	17	19	22	23		0	1	0	0	0	0	0	0	1	0	0	1	0	0	0	3
9	8	14/18	25.2/28.2	32/32.2	13/14	19/21	16/23	14/15	24/25		14	18	25.2	28.2	32	32.2	13	14	19	21	16	23	14	15	24	25		0	0	1	0	0	0	0	1	0	0	0	0	0	0	1	3
10	9	15/17	22.2/28.2	30/31	13.2/14.2	18/19	20/23	17/18	24/25		15	17	22.2	28.2	30	31	13.2	14.2	18	19	20	23	17	18	24	25		0	0	0	0	1	0	0	0	1	0	0	0	0	0	1	3
11	10	15/17	19/28.2	29/30	13/14.2	18/20	19/20	17/18	23/25		15	17	19	28.2	29	30	13	14.2	18	20	19	20	17	18	23	25		0	0	0	0	0	1	0	0	1	0	0	0	0	0	0	2
12	11	17/18	22.2/28.2	29/31	13.2/14	19/21		14/17	21/24		17	18	22.2	28.2	29	31	13.2	14	19	21	0	0	14	17	21	24		0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	1
13	12	14/16	19/26.2	29/31.2	13/14	18/19	23/24	14/16	24/25		14	16	19	26.2	29	31.2	13	14	18	19	23	24	14	16	24	25		0	1	0	0	0	0	0	1	1	0	0	1	0	0	1	5
14	13	15/16	19/27.2	30/31.2	13/14.2	18/19	19/23	14/18	23/25		15	16	19	27.2	30	31.2	13	14.2	18	19	19	23	14	18	23	25		0	1	0	0	1	0	0	0	1	0	0	0	0	0	0	3
15	14	15/15	19/27.2	29/31.2	13/14	18/19	19/23	14/16	23/24		15	15	19	27.2	29	31.2	13	14	18	19	19	23	14	16	23	24		0	0	0	0	0	0	0	1	1	0	0	0	0	0	0	2
16	15	15/16	28.2/29.2	29/29	13/13	21/22	19/23	16/19	23/24		15	16	28.2	29.2	29	29	13	13	21	22	19	23	16	19	23	24		0	1	0	0	0	0	0	0	0	0	0	0	1	0	0	2
17	16	16/18	19/21	32/33.2	13/14.2	17/18	19/20	18/19	23/24		16	18	19	21	32	33.2	13	14.2	17	18	19	20	18	19	23	24		1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
18	17	15/15	23.2/28.2	30/30	14.2/14.2	18/19	24/24	14/18	19/20		15	15	23.2	28.2	30	30	14.2	14.2	18	19	24	24	14	18	19	20		0	0	0	0	1	1	0	0	1	0	0	1	0	0	0	4
19	18	16/17	20/25.2	30/31.2	12.2/15.2	18/20	19/20	16/18	20/23		16	17	20	25.2	30	31.2	12.2	15.2	18	20	19	20	16	18	20	23		1	0	0	0	1	0	0	0	1	0	0	0	1	0	0	4
20	19	14/15	19/30.2	31.2/33.2		19/19	19/24	14/17	24/25		14	15	19	30.2	31.2	33.2	0	0	19	19	19	24	14	17	24	25		0	0	0	1	0	0	0	0	0	0	0	1	0	0	1	3
21	20	15/16	26.2/27.2	29/30	13/14	18/18	23/23	16/18	23/24		15	16	26.2	27.2	29	30	13	14	18	18	23	23	16	18	23	24		0	1	0	0	0	1	0	1	1	0	0	0	1	0	0	5
22	21		19/30.2	29/30	15/16.2	18/19	19/23	16/19	22/23		0	0	19	30.2	29	30	15	16.2	18	19	19	23	16	19	22	23		0	0	0	1	0	1	0	0	1	0	0	0	1	0	0	4
Sheet1

Cell Formulas
Range	Formula
K2:Z22	K2	=LET(rng,BYROW(MAP(B2:I22,LAMBDA(x,IF(x = "","0/0",x))),LAMBDA(x,TEXTJOIN("/",FALSE,x))), c, MAX(BYROW(rng,LAMBDA(x,LEN(x)-LEN(SUBSTITUTE(x,"/",""))+1))), xml, "<t><s>" & SUBSTITUTE(rng,"/","</s><s>") & "</s></t>", IFERROR(FILTERXML(xml,"//s[" & SEQUENCE(,c) & "]"),""))
AB3:AP22	AB3	=IF(K3:K22=K2,1,0)
AQ3:AQ22	AQ3	=SUM(AB3:AP3)
Dynamic array formulas.

 

[Fluff]

How about

=SUM(MAP($B$2:$V$2,B3:V3,LAMBDA(a,b,LET(x,TEXTSPLIT(b,"/"),y,SUM(--ISNUMBER(FIND("/"&TEXTSPLIT(b,"/",,1)&"/","/"&a&"/"))),IF(INDEX(x,1)=INDEX(x,2),IF(b=a,y,y/2),y)))))

 

[Fluff]

A slightly simplified version

=SUM(MAP($B$2:$V$2,B3:V3,LAMBDA(a,b,LET(x,TEXTSPLIT(b,"/"),y,SUM(--ISNUMBER(FIND("/"&TEXTSPLIT(b,"/",,1)&"/","/"&a&"/"))),IF((INDEX(x,1)=INDEX(x,2))*(b<>a),y/2,y)))))

 

[keda duck]

I have been playing with this, does the answer column below give the correct answer for the data i have been using?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1	no.	data 1	data 2	data 3	data 4	data 5	data 6	data 7	data 8
2	1	16/16	25.2/30.2	30/30	14/14	18/27	17/24	16/20	24/24		16	16	25.2	30.2	30	30	14	14	18	27	17	24	16	20	24	24																	Answer
3	2	15/17	19/28.2	30/31	13/14	17/23	19/23	16/20	22/22		15	17	19	28.2	30	31	13	14	17	23	19	23	16	20	22	22		0	0	0	0	1	0	0	1	0	0	0	0	1	1	0	4
4	3	15/16	22/28.2	30/32	12/15.2	19/23	24/25	15/19	21/22		15	16	22	28.2	30	32	12	15.2	19	23	24	25	15	19	21	22		0	1	0	0	1	0	0	0	0	0	0	0	0	0	0	2
5	4	17/17	20/27.2	29/31.2	13/15	18/20	17/22	14/17	22/23		17	17	20	27.2	29	31.2	13	15	18	20	17	22	14	17	22	23		0	0	0	0	0	0	0	0	1	0	1	0	0	0	0	2
6	5	16/16	25.2/26.2	29/30	13/15.2	19/19	16/20	14/18	24/24		16	16	25.2	26.2	29	30	13	15.2	19	19	16	20	14	18	24	24		1	1	1	0	0	1	0	0	0	0	0	0	0	0	1	5
7	6	15/16	19.2/24.2	29/32.2	13/13	21/23	23/24	14/19	23/25		15	16	19.2	24.2	29	32.2	13	13	21	23	23	24	14	19	23	25		0	1	0	0	0	0	0	0	0	0	0	1	0	0	0	2
8	7	15/16	22/24.2	31.2/32	13/15.2	18/23	22/24	17/19	22/23		15	16	22	24.2	31.2	32	13	15.2	18	23	22	24	17	19	22	23		0	1	0	0	0	0	0	0	1	0	0	1	0	0	0	3
9	8	14/18	25.2/28.2	32/32.2	13/14	19/21	16/23	14/15	24/25		14	18	25.2	28.2	32	32.2	13	14	19	21	16	23	14	15	24	25		0	0	1	0	0	0	0	1	0	0	0	0	0	0	1	3
10	9	15/17	22.2/28.2	30/31	13.2/14.2	18/19	20/23	17/18	24/25		15	17	22.2	28.2	30	31	13.2	14.2	18	19	20	23	17	18	24	25		0	0	0	0	1	0	0	0	1	0	0	0	0	0	1	3
11	10	15/17	19/28.2	29/30	13/14.2	18/20	19/20	17/18	23/25		15	17	19	28.2	29	30	13	14.2	18	20	19	20	17	18	23	25		0	0	0	0	0	1	0	0	1	0	0	0	0	0	0	2
12	11	17/18	22.2/28.2	29/31	13.2/14	19/21		14/17	21/24		17	18	22.2	28.2	29	31	13.2	14	19	21	0	0	14	17	21	24		0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	1
13	12	14/16	19/26.2	29/31.2	13/14	18/19	23/24	14/16	24/25		14	16	19	26.2	29	31.2	13	14	18	19	23	24	14	16	24	25		0	1	0	0	0	0	0	1	1	0	0	1	0	0	1	5
14	13	15/16	19/27.2	30/31.2	13/14.2	18/19	19/23	14/18	23/25		15	16	19	27.2	30	31.2	13	14.2	18	19	19	23	14	18	23	25		0	1	0	0	1	0	0	0	1	0	0	0	0	0	0	3
15	14	15/15	19/27.2	29/31.2	13/14	18/19	19/23	14/16	23/24		15	15	19	27.2	29	31.2	13	14	18	19	19	23	14	16	23	24		0	0	0	0	0	0	0	1	1	0	0	0	0	0	0	2
16	15	15/16	28.2/29.2	29/29	13/13	21/22	19/23	16/19	23/24		15	16	28.2	29.2	29	29	13	13	21	22	19	23	16	19	23	24		0	1	0	0	0	0	0	0	0	0	0	0	1	0	0	2
17	16	16/18	19/21	32/33.2	13/14.2	17/18	19/20	18/19	23/24		16	18	19	21	32	33.2	13	14.2	17	18	19	20	18	19	23	24		1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
18	17	15/15	23.2/28.2	30/30	14.2/14.2	18/19	24/24	14/18	19/20		15	15	23.2	28.2	30	30	14.2	14.2	18	19	24	24	14	18	19	20		0	0	0	0	1	1	0	0	1	0	0	1	0	0	0	4
19	18	16/17	20/25.2	30/31.2	12.2/15.2	18/20	19/20	16/18	20/23		16	17	20	25.2	30	31.2	12.2	15.2	18	20	19	20	16	18	20	23		1	0	0	0	1	0	0	0	1	0	0	0	1	0	0	4
20	19	14/15	19/30.2	31.2/33.2		19/19	19/24	14/17	24/25		14	15	19	30.2	31.2	33.2	0	0	19	19	19	24	14	17	24	25		0	0	0	1	0	0	0	0	0	0	0	1	0	0	1	3
21	20	15/16	26.2/27.2	29/30	13/14	18/18	23/23	16/18	23/24		15	16	26.2	27.2	29	30	13	14	18	18	23	23	16	18	23	24		0	1	0	0	0	1	0	1	1	0	0	0	1	0	0	5
22	21		19/30.2	29/30	15/16.2	18/19	19/23	16/19	22/23		0	0	19	30.2	29	30	15	16.2	18	19	19	23	16	19	22	23		0	0	0	1	0	1	0	0	1	0	0	0	1	0	0	4
Sheet1

Cell Formulas
Range	Formula
K2:Z22	K2	=LET(rng,BYROW(MAP(B2:I22,LAMBDA(x,IF(x = "","0/0",x))),LAMBDA(x,TEXTJOIN("/",FALSE,x))), c, MAX(BYROW(rng,LAMBDA(x,LEN(x)-LEN(SUBSTITUTE(x,"/",""))+1))), xml, "<t><s>" & SUBSTITUTE(rng,"/","</s><s>") & "</s></t>", IFERROR(FILTERXML(xml,"//s[" & SEQUENCE(,c) & "]"),""))
AB3:AP22	AB3	=IF(K3:K22=K2,1,0)
AQ3:AQ22	AQ3	=SUM(AB3:AP3)
Dynamic array formulas.

When I saw formula =IF(K3:K22=K2,1,0), I suspected it is wrong. This may not necessarily correspond to one column, but may require two columns to be compared to each other.

 

[keda duck]

A slightly simplified version

=SUM(MAP($B$2:$V$2,B3:V3,LAMBDA(a,b,LET(x,TEXTSPLIT(b,"/"),y,SUM(--ISNUMBER(FIND("/"&TEXTSPLIT(b,"/",,1)&"/","/"&a&"/"))),IF((INDEX(x,1)=INDEX(x,2))*(b<>a),y/2,y)))))

That is a lot closer, but still a few issues as I see it.
If a cell is empty, an error will be reported in column W. I added iferror to your formula to solve this problem.

=SUM(IFERROR(MAP($B$2:$V$2,B3:V3,LAMBDA(a,b,LET(x,TEXTSPLIT(b,"/"),y,SUM(--ISNUMBER(FIND("/"&TEXTSPLIT(b,"/",,1)&"/","/"&a&"/"))),IF((INDEX(x,1)=INDEX(x,2))*(b<>a),y/2,y)))),))

Do you have any other solutions to solve this problem?