reduce 2 formulas into one

[excelNewbie22]

hi!
can i reduce the formula in j4 to something i'm trying in j9?
i want to shorten the process cause i have a few thousands to test like this one

test
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2	1	2	3	4	5	22			1	2	3	4	5	6	15	16	17	18	22
3	2	3	9	10	11	12
4	5	6	15	16	17	18		2
5								reduce
6								it
7								to
8								this?
9								#VALUE!
test

Cell Formulas
Range	Formula
K2:U2	K2	=LET(t,VSTACK(C2:H2,C4:H4),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i))))
J4	J4	=SUM(--(COUNTIF(K2#,C3:H3)>0))
J9	J9	=SUM(--(COUNTIF(LET(t,VSTACK(C2:H2,C4:H4),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i)))),C3:H3)>0))
Dynamic array formulas.

 

[Fluff]

How about

=SUM(SIGN(COUNTIFS(C3:H3,UNIQUE(SORT(HSTACK(C2:H2,C4:H4),,,1),1))))

 

[excelNewbie22]

yet thanks again fluff,
i forgot to mention,
that sometimes i combine vertical and horizontally,
and unique don't work with both of them,
and then it count duplicates too,
anyway to solve it?

test
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	1	2	3	4	5	6			1	2	3	4	5	6	15	16	17	18
3	2	3	9	10	11	12
4	5	6	15	16	17	18		2
5	2	3	43	56	69	82		reduce
6								it
7								to
8								this?
9								4
test

Cell Formulas
Range	Formula
K2:T2	K2	=LET(t,VSTACK(C2:H2,C4:H4),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i))))
J4	J4	=SUM(--(COUNTIF(K2#,C3:H3)>0))
J9	J9	=SUM(SIGN(COUNTIFS(C3:H3,UNIQUE(SORT(HSTACK(C2:H2,C4:D5),,,1),1))))
Dynamic array formulas.

 

[Fluff]

i forgot to mention,

You have a habit of doing that. In future please make it clear exactly what you are trying to do to begin with.
Try

=SUM(SIGN(COUNTIFS(C3:H3,UNIQUE(SORT(HSTACK(C2:H2,TOROW(C4:D5)),,,1),1))))

 

[excelNewbie22]

You have a habit of doing that. In future please make it clear exactly what you are trying to do to begin with.

i know, not intentionaly, i'm really trying not to waste any of your time, and really aprriciated all your help

=SUM(SIGN(COUNTIFS(C3:H3,UNIQUE(SORT(HSTACK(C2:H2,TOROW(C4:D5)),,,1),1))))

this works, but not to sound ungrateful, is there any easier way to write all the ranges together without separated them inside the formula?
if not, this perfect too

 

[Fluff]

I have no idea what you mean by

is there any easier way to write all the ranges together without separated them inside the formula?

 

[excelNewbie22]

meaining this:

HSTACK(C2:H2,TOROW(C4:D5)

i want, if possible, to write them like that:

c2:h2,c4:d5,g2:g5

and etc

 

[Fluff]

No you can't do that as they are not all the same size/shape.

 

[excelNewbie22]

ok then,
thanks a lot fluff!

 

[excelNewbie22]

i kept trying combining the formula from my first post,
and i think i got it?
can you check and tell if this is ok?

=SUM(SIGN(COUNTIFS(C3:H3,UNIQUE(SORT(HSTACK(LET(t,VSTACK(C2:H2,C4:D5,D4:D5),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),UNIQUE(SORT(i))))),,))))

although there's some duplicate formulas inside like sort and unique