countif with criteria range (excel 2000)

Topic

New Reply

Hi lounge,

ilooked at previous posts concering the countif could not find what I was looking for right away so here is my question

How do you make the countif function count values greater than 70 and less and equal to 85 ?

Countif(Range,Criteria)

Thanks

Reply | Quote

Replies

=COUNTIF(Range,”<=85")-COUNTIF(Range,"<=70")

or

=SUMPRODUCT((Range70))

Reply | Quote

thank you

Reply | Quote

What if I want to use a cell reference in place of the absolute criterion value? Something like:
=COUNTIF(Range,”<sheet2!A10")
Another post gave me the hint to add an "=" sign after the quote, thus:
=COUNTIF(Range,"=<sheet2!A10")
and that helps in that XL swallows the formula as a valid formula but it doesn't give me the correct count.

Any clues..?

Ken

Reply | Quote

Try

=COUNTIF(Range,"<"&Sheet2!A10)

Say for example that Sheet2!A10 contains 37, then the condition “<"&Sheet2!A10 is evaluated to "<37".

Reply | Quote

and that also allows me to add in a negative sign as well:
=COUNTIF(Range,”<"&"-"&Sheet2!A10)

Ken

Reply | Quote

Or even

=COUNTIF(Range,"<-"&Sheet2!A10)

Reply | Quote

Instead of:
=COUNTIF(Range,”<"&"-"&Sheet2!A10)

You could use:
=COUNTIF(Range,"<-"&Sheet2!A10)

And save some keystrokes…

Steve

Reply | Quote