software

I'd like to count the number of cells in a range whose value is between two
preset numbers. I would imagine that it would be something like

=countif(between(a1:a10,20,30))

but there doesn't seem to be a quot;betweenquot; function.

Can anyone help? Thanks

where minimum and maximum are named cell references

=COUNTIF(A7:A17,quot;gt;quot;amp;Minimum)-COUNTIF(A7:A17,quot;gt;quot;amp;Maximum)

quot;NCINDASUNquot; wrote:

gt; I'd like to count the number of cells in a range whose value is between two
gt; preset numbers. I would imagine that it would be something like
gt;
gt; =countif(between(a1:a10,20,30))
gt;
gt; but there doesn't seem to be a quot;betweenquot; function.
gt;
gt; Can anyone help? Thanks

JMB wrote...
gt;where minimum and maximum are named cell references
gt;
gt;=COUNTIF(A7:A17,quot;gt;quot;amp;Minimum)-COUNTIF(A7:A17,quot;gt;quot;amp;Maximum)
....

That includes entries in A7:A17 equal to Maximum but not equal to
Minimum. If the OP wants to include both bounds in the count,

=COUNTIF(Rng,quot;gt;=quot;amp;Min)-COUNTIF(Rng,quot;gt;quot;amp;Max)

If the OP wants to exclude both min and max bounds from the count,

=COUNTIF(Rng,quot;gt;quot;amp;Min)-COUNTIF(Rng,quot;gt;=quot;amp;Max)

To include items equal to min but exclude items equal to max,

=COUNTIF(Rng,quot;gt;=quot;amp;Min)-COUNTIF(Rng,quot;gt;=quot;amp;Max)=IF(A1gt;A2,quot;LARGEquot;,IF(A1lt;A3,quot;SMALLquot;,quot;OKquot;))

The numeric version for an overview:
=IF(A1gt;1000,1,IF(A1lt;1000,-1,0))

quot;Harlan Grovequot; wrote:

gt; JMB wrote...
gt; gt;where minimum and maximum are named cell references
gt; gt;
gt; gt;=COUNTIF(A7:A17,quot;gt;quot;amp;Minimum)-COUNTIF(A7:A17,quot;gt;quot;amp;Maximum)
gt; ....
gt;
gt; That includes entries in A7:A17 equal to Maximum but not equal to
gt; Minimum. If the OP wants to include both bounds in the count,
gt;
gt; =COUNTIF(Rng,quot;gt;=quot;amp;Min)-COUNTIF(Rng,quot;gt;quot;amp;Max)
gt;
gt; If the OP wants to exclude both min and max bounds from the count,
gt;
gt; =COUNTIF(Rng,quot;gt;quot;amp;Min)-COUNTIF(Rng,quot;gt;=quot;amp;Max)
gt;
gt; To include items equal to min but exclude items equal to max,
gt;
gt; =COUNTIF(Rng,quot;gt;=quot;amp;Min)-COUNTIF(Rng,quot;gt;=quot;amp;Max)
gt;
gt;

ufo_pilot wrote...
....
gt;The numeric version for an overview:
gt;=IF(A1gt;1000,1,IF(A1lt;1000,-1,0))
....

So clever! But why not

=SIGN(A1-1000)

? Or don't you like short and simple?