Count match

[Kishan]

Using Excel2000

Hi,

I want tocheck row 10 data and find match, comparing with Row 6 & 7
Examplebelow shows 10 matches found in the row 10 what formula could be to apply tofind match in the cell R10?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2
3
4
5
6			2	1	1	1	X	X	1	X	X	1	1	1	2	X
7			X	2	1	2	2	X	1	1	2	2	2	1	1	1
8
9
10			2	1	1	X	2	1	1	X	2	X	2	2	2	X		10
11
12
Sheet1

Thank youin advance


Regards,
Kishan

 

[jtakw]

Hi,

This is one way, there may be better ways:

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
6	2	1	1	1	X	X	1	X	X	1	1	1	2	X
7	X	2	1	2	2	X	1	1	2	2	2	1	1	1
8
9
10	2	1	1	X	2	1	1	X	2	X	2	2	2	X		10
Sheet112

Cell Formulas
Range	Formula
R10	=SUMPRODUCT(--(C6:P6=C10:P10))+SUMPRODUCT(--(C7:P7=C10:P10))-SUMPRODUCT((C6:P6=C10:P10)*(C7:P7=C10:P10))

 

[Kishan]

Hi,

This is one way, there may be better ways:

	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
6	2	1	1	1	X	X	1	X	X	1	1	1	2	X
7	X	2	1	2	2	X	1	1	2	2	2	1	1	1
8
9
10	2	1	1	X	2	1	1	X	2	X	2	2	2	X		10

<tbody>
</tbody>

Sheet112​

Worksheet Formulas Cell Formula R10 =SUMPRODUCT(--(C6:P6=C10:P10))+SUMPRODUCT(--(C7:P7=C10:P10))-SUMPRODUCT((C6:P6=C10:P10)*(C7:P7=C10:P10)) <tbody> </tbody>

<tbody>
</tbody>

jtakw, "as you said there may be better ways". But your formula works fine

Thank you for your help

Regards,
Kishan

 

[Kishan]

Hi,

One more question does it is possible to count those match in row 10 which match in the both row 6 & 7 as the below example shows 5 matches.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2
3
4
5
6			2	1	1	1	2	X	1	X	X	1	1	2	2	X
7			X	2	1	2	2	X	1	1	2	2	2	2	1	X
8
9
10			2	1	1	X	2	1	1	X	2	X	2	2	2	X		5
11
12
Sheet2

Thank you in advance

Regards,
Kishan

 

[jtakw]

jtakw, "as you said there may be better ways". But your formula works fine

Thank you for your help

Regards,
Kishan

But then, may be Not.

Until someone comes up with a better solution, this is the Best solution.

 

[jtakw]

For your latest question:

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
6	2	1	1	1	2	X	1	X	X	1	1	2	2	X
7	X	2	1	2	2	X	1	1	2	2	2	2	1	X
8
9
10	2	1	1	X	2	1	1	X	2	X	2	2	2	X		5
Sheet113

Cell Formulas
Range	Formula
R10	=SUMPRODUCT((C6:P6=C10:P10)*(C7:P7=C10:P10))

 

[Kishan]

For your latest question:

	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
6	2	1	1	1	2	X	1	X	X	1	1	2	2	X
7	X	2	1	2	2	X	1	1	2	2	2	2	1	X
8
9
10	2	1	1	X	2	1	1	X	2	X	2	2	2	X		5

<tbody>
</tbody>

Sheet113​

Worksheet Formulas Cell Formula R10 =SUMPRODUCT((C6:P6=C10:P10)*(C7:P7=C10:P10)) <tbody> </tbody>

<tbody>
</tbody>

jtakw, your formula worked spot on.

Thank you for your Help

Have a nice weekend

Regards,
Kishan

 

[jtakw]

You're welcome.