COUNTIF with multiple criteria doesn't work

[shiro]

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.

 

[T. Valko]

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),--(D3100=D2),--(E3:E100=E2))

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

 

[shiro]

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

 

[Teethless mama]

=SUM(IF((COUNTIF(A2:A17,A2:A17)>1)*(COUNTIF(B2:B17,B2:B17)>1)*(COUNTIF(D217,D217)>1)*(COUNTIF(E2:E17,E2:E17)>1),1))

ctrl+shift+enter, not just enter

 

[ryguy7272]

Maybe you shoudl take a look at these tutorials:
http://www.contextures.com/xlPivot02.html
http://peltiertech.com/Excel/Pivots/pivottables.htm

Maybe those will give you some ideas as to what you can do with your data.

Regards,
Ryan---

 

[T. Valko]

That works as long as the serial number (column E) is unique for each date.
If there might be duplicate serial numbers for multiple dates then it
returns an incorrect result.

4/29/2008 OK J5 818 129
5/3/2008 OK J5 818 129
5/3/2008 OK J5 818 129

It might be easier to just concatenate and count the dupes:

=A1&B1&C1&D1&E1

=SUMPRODUCT(--(COUNTIF(rng,rng)>1))