Using "AND" in a COUNTIF function - Excel 97

[LIZ]

I'm trying to enter 3 ranges with 3 different ranges into
a countif - but can get it to work. Not really sure of
the layout. e.g.

=countif(and
(range&criteria1,range&criteria2,range&criteria3)) - but
it wont work - any ideas?

 

[Kelly]

You can't use AND or OR with COUNTIF. Try:
Sum(if(range&criteria, if(range&criteria,
range&criteria,1,0))) With this formula, if the criteria
is met, the cell is given a value of 1, if not, a value of
0. The function then adds up all of the 1s creating a
count. I hope that makes sense.

 

[Peo Sjoblom]

Try

=SUMPRODUCT((range={"criteria1","criteria2","criteria3"})+0)

for text

if the criteria would be numeric you have to remove the quotations

=SUMPRODUCT((range={1,2,3})+0)

If it would be greater than and less than

=SUMPRODUCT((range>=2)*(range<=10))

count numbers greater than or equal to 2 and less than or equal to 10

 

[immanuel]

Yet another method (array-entered):

{=SUM(IF((range=criteria1)*(range=criteria2)*(range=criteria3),1,0))}

/i.