Counting multiple conditions in same range

[TBertot107]

=SUMPRODUCT(COUNTIF($B$2:$F$1000,$I$2))-SUMPRODUCT(COUNTIF($B$2:$F$1000,K$1))

	​	​	​	​	​	​	​	​
​	​	​	​	​	​	​	​	​
​
​
​
​
6​

 

[Cubist]

The count should be 9. The total of 1 & 2 together. From 3 to 10 there should be 1's in there. Somehow I now see 0's.

I'm asking a hypothetical scenario with the 0's. How many 1:2 combinations in this case?

 

[TBertot107]

I'm asking a hypothetical scenario with the 0's. How many 1:2 combinations in this case?

Would probably be 1 if all the numbers after the first entry, that had 1, was replaced with 0's.

 

[TBertot107]

Would probably be 1 if all the numbers after the first entry, that had 1, was replaced with 0's.

I don't see that spreadsheet anymore.

 

[Cubist]

Maybe this.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1									1	2	3	4	5	6	7	8	9	10	11
2	1	2	17	29	35			1	9	9	3	1	2	1	1	1	1	0	0
3	1	2	13	17	27			2	9	9	3	1	2	1	1	1	1	0	0
4	1	2	13	23	27			3	3	3	3	1	0	0	0	0	1	0	0
5	1	2	3	9	18			4	1	1	1	1	0	0	0	0	0	0	0
6	1	2	5	29	33			5	2	2	0	0	2	0	0	1	0	0	0
7	1	2	5	8	31			6	1	1	0	0	0	1	1	0	0	0	0
8	1	2	3	22	33			7	1	1	0	0	0	1	1	0	0	0	0
9	1	2	6	7	30
10	1	2	3	4	17
Sheet1

Cell Formulas
Range	Formula
J2:T8	J2	=SUM(--(BYROW($B$2:$F$10,LAMBDA(b,--(COUNTIFS(b,$I2)>0)+--(COUNTIFS(b,J$1)>0)))=2))

 

[TBertot107]

Maybe this.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1									1	2	3	4	5	6	7	8	9	10	11
2	1	2	17	29	35			1	9	9	3	1	2	1	1	1	1	0	0
3	1	2	13	17	27			2	9	9	3	1	2	1	1	1	1	0	0
4	1	2	13	23	27			3	3	3	3	1	0	0	0	0	1	0	0
5	1	2	3	9	18			4	1	1	1	1	0	0	0	0	0	0	0
6	1	2	5	29	33			5	2	2	0	0	2	0	0	1	0	0	0
7	1	2	5	8	31			6	1	1	0	0	0	1	1	0	0	0	0
8	1	2	3	22	33			7	1	1	0	0	0	1	1	0	0	0	0
9	1	2	6	7	30
10	1	2	3	4	17
Sheet1

Cell Formulas
Range	Formula
J2:T8	J2	=SUM(--(BYROW($B$2:$F$10,LAMBDA(b,--(COUNTIFS(b,$I2)>0)+--(COUNTIFS(b,J$1)>0)))=2))

It appears as if this works. Just don't understand why the 1 to 1 has 9 instead of 0 as there wouldn't be any combinations of 1 & 1. If I am using the following cells in my formular how do I enter them using your formular: Looking in B2:F100 from K2 comparing to K1 & i2. If I was using the ranges B2 to F1000 how would the formular look?

 

[TBertot107]

Maybe this.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1									1	2	3	4	5	6	7	8	9	10	11
2	1	2	17	29	35			1	9	9	3	1	2	1	1	1	1	0	0
3	1	2	13	17	27			2	9	9	3	1	2	1	1	1	1	0	0
4	1	2	13	23	27			3	3	3	3	1	0	0	0	0	1	0	0
5	1	2	3	9	18			4	1	1	1	1	0	0	0	0	0	0	0
6	1	2	5	29	33			5	2	2	0	0	2	0	0	1	0	0	0
7	1	2	5	8	31			6	1	1	0	0	0	1	1	0	0	0	0
8	1	2	3	22	33			7	1	1	0	0	0	1	1	0	0	0	0
9	1	2	6	7	30
10	1	2	3	4	17
Sheet1

Cell Formulas
Range	Formula
J2:T8	J2	=SUM(--(BYROW($B$2:$F$10,LAMBDA(b,--(COUNTIFS(b,$I2)>0)+--(COUNTIFS(b,J$1)>0)))=2))

0

Maybe this.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1									1	2	3	4	5	6	7	8	9	10	11
2	1	2	17	29	35			1	9	9	3	1	2	1	1	1	1	0	0
3	1	2	13	17	27			2	9	9	3	1	2	1	1	1	1	0	0
4	1	2	13	23	27			3	3	3	3	1	0	0	0	0	1	0	0
5	1	2	3	9	18			4	1	1	1	1	0	0	0	0	0	0	0
6	1	2	5	29	33			5	2	2	0	0	2	0	0	1	0	0	0
7	1	2	5	8	31			6	1	1	0	0	0	1	1	0	0	0	0
8	1	2	3	22	33			7	1	1	0	0	0	1	1	0	0	0	0
9	1	2	6	7	30
10	1	2	3	4	17
Sheet1

Cell Formulas
Range	Formula
J2:T8	J2	=SUM(--(BYROW($B$2:$F$10,LAMBDA(b,--(COUNTIFS(b,$I2)>0)+--(COUNTIFS(b,J$1)>0)))=2))

When I changed the first F10 to F100 it came back with 121. I checked it and it appears as if it was counting the number 2 from 25 and not treating 25 as one unit. Do you understand what I am saying?

 

[TBertot107]

It appears as if this works. Just don't understand why the 1 to 1 has 9 instead of 0 as there wouldn't be any combinations of 1 & 1. If I am using the following cells in my formular how do I enter them using your formular: Looking in B2:F100 from K2 comparing to K1 & i2. If I was using the ranges B2 to F1000 how would the formular look?

I figured it out; it is counting every 1 including two digits containing the number 1. Not good.0

 

[Cubist]

I figured it out; it is counting every 1 including two digits containing the number 1. Not good.0

That is not what the formula is doing.

 

[Cubist]

This does not count 1:1 combos when there's no duplicate of that value in the same row.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1									1	2	3	4	5	6	7	8	9	10	11
2	1	2	17	29	35			1	0	9	3	1	2	1	1	1	1	0	0
3	1	2	13	17	27			2	9	0	3	1	2	1	1	1	1	0	0
4	1	2	13	23	27			3	3	3	0	1	0	0	0	0	1	0	0
5	1	2	3	9	18			4	1	1	1	0	0	0	0	0	0	0	0
6	1	2	5	29	33			5	2	2	0	0	0	0	0	1	0	0	0
7	1	2	5	8	31			6	1	1	0	0	0	0	1	0	0	0	0
8	1	2	3	22	33			7	1	1	0	0	0	1	0	0	0	0	0
9	1	2	6	7	30
10	1	2	3	4	17
Sheet1

Cell Formulas
Range	Formula
J2:T8	J2	=SUM(--(BYROW($B$2:$F$10,LAMBDA(b,IF($I2=J$1,COUNTIFS(b,$I2)>1,(--(COUNTIFS(b,$I2)>0)+--(COUNTIFS(b,J$1)>0))>=2)))))

 

[Cubist]

It appears as if this works. Just don't understand why the 1 to 1 has 9 instead of 0 as there wouldn't be any combinations of 1 & 1. If I am using the following cells in my formular how do I enter them using your formular: Looking in B2:F100 from K2 comparing to K1 & i2. If I was using the ranges B2 to F1000 how would the formular look?

Post image of the layout.