Need Formula For Repeating Values

[serge]

Hi all,

I need 2 formulas Please, to count how many numbers have been repeated from a range of data.

A1:E1 : 1 , 3 , 14 , 30 , 52
A2:E2 : 12 , 24 , 28 , 30 , 36
A3:E3 : 2 , 4 , 23 , 27 , 36
A4:E4 : 24 , 30 , 42 , 53 , 54

A5:E5 : 3, 30 , 24 , 25 , 53

1st : If I take A5:E5 How many numbers have been repeated from : A4:E4 ?

2nd : If I take A5:E5 How many numbers have been repeated from : A1:E4 ? and does not count duplicate, number 30 has showed 3 times but it would count for 1.

Thank you.

 

[serge]

1st formula should return : 3

2nd formula should return : 4

 

[serge]

Can someone give me a hand please, those formulas are important for my work.

Thanks a lot. Serge.

 

[serge]

I did those formulas but would it be a more elegant way to get the same result ? and sorry the 2nd formula result should be : 7.

1st :

=COUNTIF(A4:E4,A5)+COUNTIF(A4:E4,B5)+COUNTIF(A4:E4,C5)+COUNTIF(A4:E4,D5)+COUNTIF(A4:E4,E5)


2nd :

=COUNTIF(A1:E4,A5)+COUNTIF(A1:E4,B5)+COUNTIF(A1:E4,C5)+COUNTIF(A1:E4,D5)+COUNTIF(A1:E4,E5)

Thank you... Serge.

 

[Aladin Akyurek]

I did those formulas but would it be a more elegant way to get the same result ? and sorry the 2nd formula result should be : 7.

1st :

=COUNTIF(A4:E4,A5)+COUNTIF(A4:E4,B5)+COUNTIF(A4:E4,C5)+COUNTIF(A4:E4,D5)+COUNTIF(A4:E4,E5)


2nd :

=COUNTIF(A1:E4,A5)+COUNTIF(A1:E4,B5)+COUNTIF(A1:E4,C5)+COUNTIF(A1:E4,D5)+COUNTIF(A1:E4,E5)

Thank you... Serge.

1]

=SUMPRODUCT(COUNTIF(A5:E5,A4:E4))

2]

Either:

=SUMPRODUCT(COUNTIF(A1:E4,A5:E5))

Or:

=SUMPRODUCT(COUNTIF(A5:E5,A1:E4))

 

[serge]

Thank you very much!! Aladin, much better like this.