Formula to Count Repeat Value

[ststern45]

RepeatValuesOnce.xlsm
	R
12
Sheet2

 

[ststern45]

Please delete this post.
Having issues trying to upload Excel range XL2BB
Sorry for the trouble.

 

[ststern45]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	2	8	14	25	28		8			8	9	10	14	22		22
2	8	9	10	14	22		14			6	12	13	22	23		14
3	6	12	13	22	23		2			2	7	14	17	21		9
4	2	7	14	17	21					2	3	5	16	27		8
5	2	3	5	16	27		Total			4	9	13	16	24		Total
6	4	9	13	16	24		3			3	5	6	8	26		4
7
8	Count Repeat Values Only Once									Count Repeat Values Only Once
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K3:N6	Expression	=COUNTIF($K$1:$O$1,K3)	text	NO
J3:J6	Expression	=COUNTIF($K$1:$O$1,J3)	text	NO
K2:N2	Expression	=COUNTIF($K$1:$O$1,K2)	text	NO
J2	Expression	=COUNTIF($K$1:$O$1,J2)	text	NO
B3:E6	Expression	=COUNTIF($B$1:$F$1,B3)	text	NO
A3:A6	Expression	=COUNTIF($B$1:$F$1,A3)	text	NO
B2:E2	Expression	=COUNTIF($B$1:$F$1,B2)	text	NO
A2	Expression	=COUNTIF($B$1:$F$1,A2)	text	NO

 

[ststern45]

Please delete this post.
Having issues trying to upload Excel range XL2BB
Sorry for the trouble.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	2	8	14	25	28		8			8	9	10	14	22		22
2	8	9	10	14	22		14			6	12	13	22	23		14
3	6	12	13	22	23		2			2	7	14	17	21		9
4	2	7	14	17	21					2	3	5	16	27		8
5	2	3	5	16	27		Total			4	9	13	16	24		Total
6	4	9	13	16	24		3			3	5	6	8	26		4
7
8	Count Repeat Values Only Once									Count Repeat Values Only Once
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K3:N6	Expression	=COUNTIF($J$1:$N$1,K3)	text	NO
J3:J6	Expression	=COUNTIF($J$1:$N$1,J3)	text	NO
K2:N2	Expression	=COUNTIF($J$1:$N$1,K2)	text	NO
A3:A6	Expression	=COUNTIF($A$1:$E$1,A3)	text	NO
B2:E6	Expression	=COUNTIF($A$1:$E$1,B2)	text	NO
J2	Expression	=COUNTIF($J$1:$N$1,J2)	text	NO
A2	Expression	=COUNTIF($A$1:$E$1,A2)	text	NO

Cell Range A1 through E1 contains the values 2, 8, 14, 25, 28
I've tried a number of formulas and I'm stuck.
I'm trying to count how many values from cell range A2 through E6 are also in cell range A1 through E1.
The result should be 3
8, 14, 2 (14 & 2 are repeats and not counted more than once)
Counting values only once

Example #2
Cell range J1 through N1 contain the values are 8, 9, 10, 14, 22
Cell range J2 through N6 contain 4 values that also appear in cell range J1:N1
There are no repeat values so the result would be 4
22, 14, 9, 8

Thank you in advance

 

[Peter_SSs]

Does this do what you want?

23 07 03.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	2	8	14	25	28					8	9	10	14	22
2	8	9	10	14	22					6	12	13	22	23
3	6	12	13	22	23					2	7	14	17	21
4	2	7	14	17	21					2	3	5	16	27
5	2	3	5	16	27		Total			4	9	13	16	24		Total
6	4	9	13	16	24		3			3	5	6	8	26		4
Count

Cell Formulas
Range	Formula
G6,P6	G6	=SUM(--(COUNTIF(A2:E6,A1:E1)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ststern45]

Does this do what you want?

23 07 03.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	2	8	14	25	28					8	9	10	14	22
2	8	9	10	14	22					6	12	13	22	23
3	6	12	13	22	23					2	7	14	17	21
4	2	7	14	17	21					2	3	5	16	27
5	2	3	5	16	27		Total			4	9	13	16	24		Total
6	4	9	13	16	24		3			3	5	6	8	26		4
Count

Cell Formulas
Range	Formula
G6,P6	G6	=SUM(--(COUNTIF(A2:E6,A1:E1)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you Peter.
Yes this works like a charm.
Appreciate your time and effort.
Thank you

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Peter_SSs]

Yes this works like a charm.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.