Compact the formula instead of repeating

[Stephen_IV]

Is there a better way to do this than repeating with a dynamic array? Thanks in advance!

=HSTACK(
COUNTIF($B$2:$B$7,SEQUENCE(10)),
COUNTIF($C$2:$C$7,SEQUENCE(10)),
COUNTIF($D$2:$D$7,SEQUENCE(10)),
COUNTIF($E$2:$E$7,SEQUENCE(10)),
COUNTIF($F$2:$F$7,SEQUENCE(10))
)

 

[hagia_sofia]

Hello, and what is it that you are trying to achieve?

 

[hagia_sofia]

Maybe something like this?

=LET(
a,B2:F7,
b,COLUMNS(a),
WRAPROWS(BYROW(WRAPROWS(TOCOL(--(SEQUENCE(10)=TOROW(a,,TRUE))),b),SUM),b))

 

[Cubist]

Maybe this:

Book2
	B	C	D	E	F	G	H	I	J	K	L
1
2	3	6	4	8	3		0	0	0	1	0
3	3	10	4	7	0		0	0	0	1	0
4	9	5	9	1	0		2	0	0	0	1
5	6	7	10	6	4		1	0	2	0	2
6	4	6	5	2	6		0	1	1	1	0
7	0	6	9	5	4		1	3	0	1	1
8							0	1	0	1	0
9							0	0	0	1	0
10							1	0	2	0	0
11							0	1	1	0	0
Sheet3

Cell Formulas
Range	Formula
H2:L11	H2	=MAKEARRAY(10,COLUMNS(B2:F7),LAMBDA(r,c,COUNTIF(INDEX(B2:F7,0,c),r)))
Dynamic array formulas.

 

[Stephen_IV]

Cubist that is it!!!! Sorry, I should have posted the data with the question. Thank you so much!!!

Contestant	judge 1	judge 2	judge 3	judge 4	judge 5
Joe	10	10	10	10	9
PJ	10	9	9	9	10
Tom	9	10	9	10	10
Sue	10	9	10	10	9
Sally	10	10	10	10	10
Billy	10	9	8	10	10

Answer that I am looking for

0	0	0	0	0
0	0	0	0	0
0	0	0	0	0
0	0	0	0	0
0	0	0	0	0
0	0	0	0	0
0	0	0	0	0
0	0	1	0	0
1	3	2	1	2
5	3	3	5	4

 

[Cubist]

You're welcome. Glad we can assist.

 

[djclements]

Maybe something like this?

=LET(a,B2:F7,b,COLUMNS(a),WRAPROWS(BYROW(WRAPROWS(TOCOL(--(SEQUENCE(10)=TOROW(a,,TRUE))),b),SUM),b))

Nice concept! The only misstep was the wrap_count argument of the nested WRAPROWS function. It should be ROWS(a):

=LET(a,B2:F7,WRAPROWS(BYROW(WRAPROWS(TOCOL(--(SEQUENCE(10)=TOROW(a,,1))),ROWS(a)),SUM),COLUMNS(a)))

MMULT could also work:

=MMULT(--(TOROW(B2:F7)=SEQUENCE(10)),--(TOCOL(IF({1},B1:F1,B2:F7))=B1:F1))

Or, if the column headers in B1:F1 were either not present or not unique:

=LET(a,B2:F7,c,SEQUENCE(,COLUMNS(a)),MMULT(--(TOROW(a)=SEQUENCE(10)),--(TOCOL(IF({1},c,a))=c)))

Of course there's also REDUCE:

=LET(n,SEQUENCE(10),REDUCE(n,SEQUENCE(COLUMNS(B2:F7)),LAMBDA(a,c,HSTACK(a,COUNTIF(INDEX(B2:F7,,c),n)))))

Or with THUNKS instead of INDEX:

=LET(n,SEQUENCE(10),REDUCE(n,BYCOL(B2:F7,LAMBDA(c,LET(x,COUNTIF(c,n),LAMBDA(x)))),LAMBDA(a,λ,HSTACK(a,λ()))))

Cheers!

 

[hagia_sofia]

Nice concept! The only misstep was the wrap_count argument of the nested WRAPROWS function. It should be ROWS(a):

=LET(a,B2:F7,WRAPROWS(BYROW(WRAPROWS(TOCOL(--(SEQUENCE(10)=TOROW(a,,1))),ROWS(a)),SUM),COLUMNS(a)))

MMULT could also work:

=MMULT(--(TOROW(B2:F7)=SEQUENCE(10)),--(TOCOL(IF({1},B1:F1,B2:F7))=B1:F1))

Or, if the column headers in B1:F1 were either not present or not unique:

=LET(a,B2:F7,c,SEQUENCE(,COLUMNS(a)),MMULT(--(TOROW(a)=SEQUENCE(10)),--(TOCOL(IF({1},c,a))=c)))

Of course there's also REDUCE:

=LET(n,SEQUENCE(10),REDUCE(n,SEQUENCE(COLUMNS(B2:F7)),LAMBDA(a,c,HSTACK(a,COUNTIF(INDEX(B2:F7,,c),n)))))

Or with THUNKS instead of INDEX:

=LET(n,SEQUENCE(10),REDUCE(n,BYCOL(B2:F7,LAMBDA(c,LET(x,COUNTIF(c,n),LAMBDA(x)))),LAMBDA(a,λ,HSTACK(a,λ()))))

Cheers!

Many thanks for the correction, @djclements! The concept was of course inspired by your previous posts which I try to follow closely; the same goes for @Cubist as I learn something new from both of you...

 

[Stephen_IV]

Thanks to everyone for helping and sharing your knowledge. I appreciate it!