Dear Mr.T.Valko,
It still doesn't return the value that I want.May be I describe
my situation unclearly,but thank's for response.
Let see my sample below data.



Date Status Area code Area No Serial No Repeated data
4/29/2008 OK J5 818 125
4/30/2008 OK J5 818 126
5/1/2008 NG J5 818 127
5/2/2008 OK J5 818 128
5/3/2008 OK J5 818 129
5/4/2008 NG J5 818 130
5/5/2008 OK J5 818 131
5/6/2008 OK J5 818 132
4/29/2008 NG J5 818 133
4/30/2008 OK J5 818 134
5/1/2008 OK J5 818 135
5/2/2008 NG J5 818 136
5/3/2008 OK J5 818 129
5/4/2008 OK J5 818 122
5/5/2008 NG J5 818 115
5/6/2008 OK J5 818 108


Actually there are 2 excatly similar data,how to insert the formula
in the column/cell "Repeated data",so that it return 2.

thank's in advance.

Rgds,

Shiro


"T. Valko" wrote in message
...
Try this:

A2:E2 are your criteria:

A2 = some date
B2 = some text value
C2 = some text value
D2 = some number
E2 = some number


=SUMPRODUCT(--(A3:A100=A2),--(B3:B100=B2),--(C3:C100=C2),--(D3:D100=D2),--(E
3:E100=E2))

Note that you can't use entire columns as range references in SUMPRODUCT
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP


"shiro" wrote in message
...
Hi all,
I'm trying to find the number of match value in a range
of cell by using multiple criteria,but can not get
the result that I want.I use COUNTIF function like below
but doesn't work:
=COUNTIF(A:E,AND(A2,B2,C2,D2,E2))

How to solve it.The function always return 0 (zero)although
there are similar value.Bu if I put single criteria,it works fine.

I work with Excel 2000 and the data type for each column is:

Column A : Date
Column B : Text
Column C : Text
Column D : Number
Column E : Number

Please helphow to corrected the formula.

Rgds,

Shiro.