# Thread: countif with criteria range (excel 2000)

1. ## countif with criteria range (excel 2000)

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

2. ## Re: countif with criteria range (excel 2000)

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

or

=SUMPRODUCT((Range<=85)*(Range>70))

thank you

4. ## Re: countif with criteria range (excel 2000)

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

5. ## Re: countif with criteria range (excel 2000)

Try
<code>
=COUNTIF(Range,"<"&Sheet2!A10)
</code>
Say for example that Sheet2!A10 contains 37, then the condition "<"&Sheet2!A10 is evaluated to "<37".

6. ## Re: countif with criteria range (excel 2000)

<img src=/S/thewave.gif border=0 alt=thewave width=225 height=33>

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

<img src=/S/thewave.gif border=0 alt=thewave width=225 height=33>

Ken

7. ## Re: countif with criteria range (excel 2000)

Or even
<code>
=COUNTIF(Range,"<-"&Sheet2!A10)</code>

8. ## Re: countif with criteria range (excel 2000)

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

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

And save some keystrokes...

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•