Put

=(COUNTIF($A3:$D3,$A$1)0)+(COUNTIF($A3:$D3,$B$1) 0)+(COUNTIF($A3:$D3,$C$1)
0)+(COUNTIF($A3:$D3,$D$1)0)

in E3 and drag fill down the column.

--
Regards,
Tom Ogilvy



Michael168 wrote in message
...
Excel Formula or VBA code help needed
Hi! Can any one of you help me ?

DataCell = A1:D1

RangeCell = A3:D714 etc

I want to count no of times the non duplicate numbers from Cell A1:A4
have been repeated against the non duplicate numbers in Cell
A3:D3,A4:D4,,,etc until A714:D714 row by row and have the sum result in
E3:E714

A B C D E
1 3 0 2

0 0 0 0 1
0 0 1 2 3
0 0 0 2 2
0 0 2 2 2
0 1 2 3 4
4 5 6 7 0

In E2:E6 will hold the results as example above.
I have try using the array formula =Sum(If(A1:D1=A8:D8)) ,with row1
against 3,4,5,6,7 but the answers were not correct always.

Thank you for your help.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/