shorten 2 divide formulas

[excelNewbie22]

hi, how can i shorten this into one? but by counting the true ones (sum?)

test.xlsb
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2	1	2	3	4	5	6	FALSE	FALSE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	TRUE	FALSE	TRUE	TRUE	FALSE	TRUE	TRUE
test

Cell Formulas
Range	Formula
I2	I2	=MOD(H2,G2)=0
J2	J2	=MOD(H2,F2)=0
K2	K2	=MOD(H2,E2)=0
L2	L2	=MOD(H2,D2)=0
M2	M2	=MOD(H2,C2)=0
N2	N2	=MOD(G2,F2)=0
O2	O2	=MOD(G2,E2)=0
P2	P2	=MOD(G2,D2)=0
Q2	Q2	=MOD(G2,C2)=0
R2	R2	=MOD(F2,E2)=0
S2	S2	=MOD(F2,D2)=0
T2	T2	=MOD(F2,C2)=0
U2	U2	=MOD(E2,D2)=0
V2	V2	=MOD(E2,C2)=0
W2	W2	=MOD(D2,C2)=0

same thing to this one?

test.xlsb
	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	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB
2	1	2	3	4	5	6	TRUE	TRUE	FALSE	TRUE	FALSE	FALSE	TRUE	TRUE	FALSE	TRUE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	TRUE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
3	7	8	9	10	11	12	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE
test

Cell Formulas
Range	Formula
I2	I2	=MOD(C1,C2)=0
J2	J2	=MOD(C1,D2)=0
K2	K2	=MOD(C1,E2)=0
L2	L2	=MOD(C1,F2)=0
M2	M2	=MOD(C1,G2)=0
N2	N2	=MOD(C1,H2)=0
O2	O2	=MOD(D1,C2)=0
P2	P2	=MOD(D1,D2)=0
Q2	Q2	=MOD(D1,E2)=0
R2	R2	=MOD(D1,F2)=0
S2	S2	=MOD(D1,G2)=0
T2	T2	=MOD(D1,H2)=0
U2	U2	=MOD(E1,C2)=0
V2	V2	=MOD(E1,D2)=0
W2	W2	=MOD(E1,E2)=0
X2	X2	=MOD(E1,F2)=0
Y2	Y2	=MOD(E1,G2)=0
Z2	Z2	=MOD(E1,H2)=0
AA2	AA2	=MOD(F1,C2)=0
AB2	AB2	=MOD(F1,D2)=0
AC2	AC2	=MOD(F1,E2)=0
AD2	AD2	=MOD(F1,F2)=0
AE2	AE2	=MOD(F1,G2)=0
AF2	AF2	=MOD(F1,H2)=0
AG2	AG2	=MOD(G1,C2)=0
AH2	AH2	=MOD(G1,D2)=0
AI2	AI2	=MOD(G1,E2)=0
AJ2	AJ2	=MOD(G1,F2)=0
AK2	AK2	=MOD(G1,G2)=0
AL2	AL2	=MOD(G1,H2)=0
AM2	AM2	=MOD(H1,C2)=0
AN2	AN2	=MOD(H1,D2)=0
AO2	AO2	=MOD(H1,E2)=0
AP2	AP2	=MOD(H1,F2)=0
AQ2	AQ2	=MOD(H1,G2)=0
AR2	AR2	=MOD(H1,H2)=0
AS2	AS2	=MOD(C2,C1)=0
AT2	AT2	=MOD(C2,D1)=0
AU2	AU2	=MOD(C2,E1)=0
AV2	AV2	=MOD(C2,F1)=0
AW2	AW2	=MOD(C2,G1)=0
AX2	AX2	=MOD(C2,H1)=0
AY2	AY2	=MOD(D2,C1)=0
AZ2	AZ2	=MOD(D2,D1)=0
BA2	BA2	=MOD(D2,E1)=0
BB2	BB2	=MOD(D2,F1)=0
BC2	BC2	=MOD(D2,G1)=0
BD2	BD2	=MOD(D2,H1)=0
BE2	BE2	=MOD(E2,C1)=0
BF2	BF2	=MOD(E2,D1)=0
BG2	BG2	=MOD(E2,E1)=0
BH2	BH2	=MOD(E2,F1)=0
BI2	BI2	=MOD(E2,G1)=0
BJ2	BJ2	=MOD(E2,H1)=0
BK2	BK2	=MOD(F2,C1)=0
BL2	BL2	=MOD(F2,D1)=0
BM2	BM2	=MOD(F2,E1)=0
BN2	BN2	=MOD(F2,F1)=0
BO2	BO2	=MOD(F2,G1)=0
BP2	BP2	=MOD(F2,H1)=0
BQ2	BQ2	=MOD(G2,C1)=0
BR2	BR2	=MOD(G2,D1)=0
BS2	BS2	=MOD(G2,E1)=0
BT2	BT2	=MOD(G2,F1)=0
BU2	BU2	=MOD(G2,G1)=0
BV2	BV2	=MOD(G2,H1)=0
BW2	BW2	=MOD(H2,C1)=0
BX2	BX2	=MOD(H2,D1)=0
BY2	BY2	=MOD(H2,E1)=0
BZ2	BZ2	=MOD(H2,F1)=0
CA2	CA2	=MOD(H2,G1)=0
CB2,BW3	CB2	=MOD(H2,H1)=0
I3	I3	=MOD(C2,D2)=0
J3	J3	=MOD(C2,E2)=0
K3	K3	=MOD(C2,F2)=0
L3	L3	=MOD(C2,G2)=0
M3	M3	=MOD(C2,H2)=0
N3	N3	=MOD(C2,C3)=0
O3	O3	=MOD(C2,D3)=0
P3	P3	=MOD(C2,E3)=0
Q3	Q3	=MOD(C2,F3)=0
R3	R3	=MOD(C2,G3)=0
S3	S3	=MOD(C2,H3)=0
T3	T3	=MOD(D2,C2)=0
U3	U3	=MOD(D2,E2)=0
V3	V3	=MOD(D2,F2)=0
W3	W3	=MOD(D2,G2)=0
X3	X3	=MOD(D2,H2)=0
Y3	Y3	=MOD(D2,C3)=0
Z3	Z3	=MOD(D2,D3)=0
AA3	AA3	=MOD(D2,E3)=0
AB3	AB3	=MOD(D2,F3)=0
AC3	AC3	=MOD(D2,G3)=0
AD3	AD3	=MOD(D2,H3)=0
AE3	AE3	=MOD(E2,C2)=0
AF3	AF3	=MOD(E2,D2)=0
AG3	AG3	=MOD(E2,F2)=0
AH3	AH3	=MOD(E2,G2)=0
AI3	AI3	=MOD(E2,H2)=0
AJ3	AJ3	=MOD(E2,C3)=0
AK3	AK3	=MOD(E2,D3)=0
AL3	AL3	=MOD(E2,E3)=0
AM3	AM3	=MOD(E2,F3)=0
AN3	AN3	=MOD(E2,G3)=0
AO3	AO3	=MOD(E2,H3)=0
AP3	AP3	=MOD(F2,C2)=0
AQ3	AQ3	=MOD(F2,D2)=0
AR3	AR3	=MOD(F2,E2)=0
AS3	AS3	=MOD(F2,G2)=0
AT3	AT3	=MOD(F2,H2)=0
AU3	AU3	=MOD(F2,C3)=0
AV3	AV3	=MOD(F2,D3)=0
AW3	AW3	=MOD(F2,E3)=0
AX3	AX3	=MOD(F2,F3)=0
AY3	AY3	=MOD(F2,G3)=0
AZ3	AZ3	=MOD(F2,H3)=0
BA3	BA3	=MOD(G2,C2)=0
BB3	BB3	=MOD(G2,D2)=0
BC3	BC3	=MOD(G2,E2)=0
BD3	BD3	=MOD(G2,F2)=0
BE3	BE3	=MOD(G2,H2)=0
BF3	BF3	=MOD(G2,C3)=0
BG3	BG3	=MOD(G2,D3)=0
BH3	BH3	=MOD(G2,E3)=0
BI3	BI3	=MOD(G2,F3)=0
BJ3	BJ3	=MOD(G2,G3)=0
BK3	BK3	=MOD(G2,H3)=0
BL3	BL3	=MOD(H2,C2)=0
BM3	BM3	=MOD(H2,D2)=0
BN3	BN3	=MOD(H2,E2)=0
BO3	BO3	=MOD(H2,F2)=0
BP3	BP3	=MOD(H2,G2)=0
BQ3	BQ3	=MOD(H2,C3)=0
BR3	BR3	=MOD(H2,D3)=0
BS3	BS3	=MOD(H2,E3)=0
BT3	BT3	=MOD(H2,F3)=0
BU3	BU3	=MOD(H2,G3)=0
BV3	BV3	=MOD(H2,H3)=0
BX3	BX3	=MOD(C3,D2)=0
BY3	BY3	=MOD(C3,E2)=0
BZ3	BZ3	=MOD(C3,F2)=0
CA3	CA3	=MOD(C3,G2)=0
CB3	CB3	=MOD(C3,H2)=0

 

[Fluff]

anyone can help with the two rows? c2:h3 ?

Not really as the formulae for the two rows are all over the place & make no sense.

 

[Dave Patton]

anyone can help with the two rows? c2:h3 ? Did you try the suggestions? Can you clarify your requirement?

T202212a.xlsm
	A	B	C	D	E	F	G	H
2	8	8	1	2	3	4	5	6
3	2	2	3	8	9	10	11	12
4	11	11	1	2	4	4	6	6
5	2	2	4	8	9	10	11	12
1f

Cell Formulas
Range	Formula
A2:A5	A2	=SUM(MAKEARRAY(1,5,LAMBDA(r,c,SUM(--(MOD(INDEX(C2:H2,,c+1),DROP(C2:H2,,c-6))=0)))))
B2:B5	B2	=SUM(--(MOD(H2,C2:G2)=0),--(MOD(G2,C2:F2)=0),--(MOD(F2,C2:E2)=0),--(MOD(E2,C2:D2)=0),--(MOD(D2,C2)=0))

 

[Eric W]

Something like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2			1	2	3	4	5	6		8
3			7	8	9	10	11	12		0
4
5										23	2 rows
Sheet1

Cell Formulas
Range	Formula
J2:J3	J2	=LET(rng,C2:H2,t,TRANSPOSE(rng),SUM((rng>t)*(MOD(rng,t)=0)))
J5	J5	=LET(rng,C2:H3,r,SMALL(rng,SEQUENCE(COUNT(rng))),t,TRANSPOSE(r),SUM((r>t)*(MOD(r,t)=0)))

Note that these formulas do not require the numbers to be in ascending order, but it will give erroneous results if there are duplicate values in a row. I can probably figure that out if needed.

 

[excelNewbie22]

Eric W, great! thank you!

is there no way of marking two solutions?
i think there's the need to do so

p.s
there isn't two identical numbers in a row

 

[excelNewbie22]

eric, is it possible to find only the dividable numbers between the two rows?
like c2 against c3 and d3 and e3 and f3 and g3 and h3 and etc
but without the c2 against d2 and e2 and etc
?
if not, still good

 

[Eric W]

How do you want to identify them? By Conditional Formatting, or maybe a list?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2			29	2	3	4	5	6		3
3			7	8	9	10	11	12		0
4
5										12	2 rows
6
7										4
8										6
9										8
10										9
11										10
12										12
13
Sheet1

Cell Formulas
Range	Formula
J2:J3	J2	=LET(rng,C2:H2,t,TRANSPOSE(rng),SUM((rng>t)*(MOD(rng,t)=0)))
J5	J5	=LET(rng,C2:H3,r,SMALL(rng,SEQUENCE(COUNT(rng))),t,TRANSPOSE(r),SUM((r>t)*(MOD(r,t)=0)))
J7:J12	J7	=LET(rng,C2:H3,s,SEQUENCE(COUNT(rng)),r,SMALL(rng,s),t,TRANSPOSE(r),f,(MOD(r,t)=0)*(r>t),m,MMULT(f,s),FILTER(r,m))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:H3	Expression	=LET(rng,$C$2:$H$3,r,SMALL(rng,SEQUENCE(COUNT(rng))),OR((r<D2)*(MOD(D2,r)=0)))	text	NO

 

[excelNewbie22]

eric, is it possible to find only the dividable numbers between the two rows?
like c2 against c3 and d3 and e3 and f3 and g3 and h3 and etc
but without the c2 against d2 and e2 and etc

sorry again, i meant counting only the dividable numbers between the two rows?
and for the example you gave the result should be 9

 

[Eric W]

There's probably a way. But first, how did you get the result of 9 for my example? What numbers specifically should be included in the count, and why?

 

[excelNewbie22]

29-2-3-4-5-6
7-8-9-10-11-12

so, 12 divide by 2 and 3 and 4 and 6 = 4
plus
10 divide by 5 and 2 = 2
plus
9 by3 = 1
plus
8 by 4 and 2 =2
total = 9

11 and 7 and 29 don't divide by any of the above numbers

including always the larger number and checking if it's divide by any of the smaller one's with a remainder
but only from one row to another, not inside one row (which you already gave a solution) like 6 divide by 3

 

[Dave Patton]

A quick guess.

T202212a.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
12	2	3	4	5	6	7	8	9	10	11	12	29
13		yes		yes		yes				yes		yes
14
1f

Cell Formulas
Range	Formula
C13:M13	C13	=IF(IFERROR(SUM(--(MOD(C12,$B12:B12)=0)),"")=0,"yes","")