Counting multiple conditions in same range

[TBertot107]

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

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

 

[awoohaw]

welcome to the forum. Is column 1 an A column or a B column.
What is the value in cells I1 and K1.

What is your expected result? (is it 6?)

 

[TBertot107]

The values in I2 is 1. The value in K1 is 2. The ranges I am looking in is B2:F1000. The total value of 1 is 121 and 2 129 respectively. It is coming back with a -8 which is the difference between them. I am trying to get the total of the combination of 1 & 2.

=SUMPRODUCT(COUNTIF($B$2:$F$1000,$I$2))-SUMPRODUCT(COUNTIF($B$2:$F$1000,K$1)) I understand why I am getting the -8 but don't know how to get the total combinations of 1 & 2. Which should be 18.

 

[TBertot107]

Not sure how to use that XL2BB or I would have copied the entire spreadsheet and sent it to you.

 

[TBertot107]

welcome to the forum. Is column 1 an A column or a B column.
What is the value in cells I1 and K1.

What is your expected result? (is it 6?)

The values in I2 is 1. The value in K1 is 2. The ranges I am looking in is B2:F1000. The total value of 1 is 121 and 2 129 respectively. It is coming back with a -8 which is the difference between them. I am trying to get the total of the combination of 1 & 2.

=SUMPRODUCT(COUNTIF($B$2:$F$1000,$I$2))-SUMPRODUCT(COUNTIF($B$2:$F$1000,K$1)) I understand why I am getting the -8 but don't know how to get the total combinations of 1 & 2. Which should be 18.

 

[TBertot107]

welcome to the forum. Is column 1 an A column or a B column.
What is the value in cells I1 and K1.

What is your expected result? (is it 6?)

 

[Cubist]

I counted nine 1's and nine 2's so why is the expected answer 9? Perhaps 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	18	18	12	10	11	10	10	10	10	9	9
3	1	2	13	17	27			2	18	18	12	10	11	10	10	10	10	9	9
4	1	2	13	23	27			3	12	12	6	4	5	4	4	4	4	3	3
5	1	2	3	9	18			4	10	10	4	2	3	2	2	2	2	1	1
6	1	2	5	29	33			5	11	11	5	3	4	3	3	3	3	2	2
7	1	2	5	8	31			6	10	10	4	2	3	2	2	2	2	1	1
8	1	2	3	22	33			7	10	10	4	2	3	2	2	2	2	1	1
9	1	2	6	7	30
10	1	2	3	4	17
Sheet1

Cell Formulas
Range	Formula
J2:T8	J2	=COUNTIFS($B$2:$F$10,$I2)+COUNTIFS($B$2:$F$10,J$1)

 

[TBertot107]

I counted nine 1's and nine 2's so why is the expected answer 9? Perhaps 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	18	18	12	10	11	10	10	10	10	9	9
3	1	2	13	17	27			2	18	18	12	10	11	10	10	10	10	9	9
4	1	2	13	23	27			3	12	12	6	4	5	4	4	4	4	3	3
5	1	2	3	9	18			4	10	10	4	2	3	2	2	2	2	1	1
6	1	2	5	29	33			5	11	11	5	3	4	3	3	3	3	2	2
7	1	2	5	8	31			6	10	10	4	2	3	2	2	2	2	1	1
8	1	2	3	22	33			7	10	10	4	2	3	2	2	2	2	1	1
9	1	2	6	7	30
10	1	2	3	4	17
Sheet1

Cell Formulas
Range	Formula
J2:T8	J2	=COUNTIFS($B$2:$F$10,$I2)+COUNTIFS($B$2:$F$10,J$1)

000

I counted nine 1's and nine 2's so why is the expected answer 9? Perhaps 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	18	18	12	10	11	10	10	10	10	9	9
3	1	2	13	17	27			2	18	18	12	10	11	10	10	10	10	9	9
4	1	2	13	23	27			3	12	12	6	4	5	4	4	4	4	3	3
5	1	2	3	9	18			4	10	10	4	2	3	2	2	2	2	1	1
6	1	2	5	29	33			5	11	11	5	3	4	3	3	3	3	2	2
7	1	2	5	8	31			6	10	10	4	2	3	2	2	2	2	1	1
8	1	2	3	22	33			7	10	10	4	2	3	2	2	2	2	1	1
9	1	2	6	7	30
10	1	2	3	4	17
Sheet1

Cell Formulas
Range	Formula
J2:T8	J2	=COUNTIFS($B$2:$F$10,$I2)+COUNTIFS($B$2:$F$10,J$1)

The answer should have been 9. There are 9 1 & 2 combinations in rows 2 - 10.

 

[Cubist]

000

The answer should have been 9. There are 9 1 & 2 combinations in rows 2 - 10.

Are you counting row by row? In this example, what would the expected result be?

Book1
	B	C	D	E	F
1
2	1	2	17	29	35
3	0	2	13	17	27
4	0	2	13	23	27
5	0	2	3	9	18
6	0	2	5	29	33
7	0	2	5	8	31
8	0	2	3	22	33
9	0	2	6	7	30
10	0	2	3	4	17
Sheet1

 

[TBertot107]

Are you counting row by row? In this example, what would the expected result be?

Book1
	B	C	D	E	F
1
2	1	2	17	29	35
3	0	2	13	17	27
4	0	2	13	23	27
5	0	2	3	9	18
6	0	2	5	29	33
7	0	2	5	8	31
8	0	2	3	22	33
9	0	2	6	7	30
10	0	2	3	4	17
Sheet1

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.