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)

 

[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(b="",0,IF(INDEX(x,1)=INDEX(x,2),IF(b=a,y,y/2),y))))))

 

[keda duck]

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(b="",0,IF(INDEX(x,1)=INDEX(x,2),IF(b=a,y,y/2),y))))))

These results are what I expected. I marked as solution. Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

These results are what I expected. I marked as solution.

This simplified version produces the same results for the sample data.

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

 

[keda duck]

This simplified version produces the same results for the sample data.

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

Wow, this version is simple and easy to understand. Thanks!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.