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

 

[Eric W]

The J5 formula should do that for you. I think you missed 6/3, 6/2, and 4/2 in your count of 9, so those 3 bring it up to 12, like the example shows.

Or do you mean, what is the count if you use the second row as the dividends, and the first row as divisors? Then try:

=LET(rngA,C3:H3,rngB,C2:H2,t,TRANSPOSE(rngB),SUM((rngA>t)*(MOD(rngA,t)=0)))

 

[excelNewbie22]

hi,

Or do you mean, what is the count if you use the second row as the dividends, and the first row as divisors? Then try:

yes, but i need to know the count of row 1 against #2 and vice versa, alltogether
but for that bit i can just join the two unless there's a fancier way

and also if a number repeat itself in both rows, for the formula to count it too (like 3 and 3)

for example:

test
	C	D	E	F	G	H	I	J	K
1									should be
2	1	2	3	4				7	9
3	3	4	5	6				0	2
4								total
5								7	11
test

Cell Formulas
Range	Formula
J2	J2	=LET(rngA,C3:F3,rngB,C2:F2,t,TRANSPOSE(rngB),SUM((rngA>t)*(MOD(rngA,t)=0)))
J3	J3	=LET(rngA,C2:F2,rngB,C3:F3,t,TRANSPOSE(rngB),SUM((rngA>t)*(MOD(rngA,t)=0)))
J5	J5	=LET(rngA,C3:F3,rngB,C2:F2,t,TRANSPOSE(rngB),SUM((rngA>t)*(MOD(rngA,t)=0)))+LET(rngA,C2:F2,rngB,C3:F3,t,TRANSPOSE(rngB),SUM((rngA>t)*(MOD(rngA,t)=0)))

edit:
for the part of same number in both rows, i think i got it

=LET(rngA,C3:F3,rngB,C2:F2,t,TRANSPOSE(rngB),SUM((rngA>=t)*(MOD(rngA,t)=0)))

is that right?
for the part of sum of two of the rows against each other, still not sure, what say you?

 

[excelNewbie22]

just noticed that if do it my way, like in j5, if there's duplicate number in both rows, it will count it twice, which is no good

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1											should be
2			1	2	3	4				9	9
3			3	4	5	6
Sheet1

Cell Formulas
Range	Formula
J2	J2	=LET(rngA,C3:F3,rngB,C2:F2,t,TRANSPOSE(rngB),SUM((rngA>t)*(MOD(rngA,t)=0)))+LET(rngB,C3:F3,rngA,C2:F2,t,TRANSPOSE(rngB),SUM((rngA>t)*(MOD(rngA,t)=0))+SUM(COUNTIF(rngA,rngB)))

This is becoming more and more abstruse. Can you explain what you're using this for? I've been in math and computing a long time, and I can't see how this would be useful. There might be other options.

 

[excelNewbie22]

this is it!
thanks for the time, patience, and solution!