Between? Is there a way?

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 "between" function.

Can anyone help? Thanks

where minimum and maximum are named cell references

=COUNTIF(A7:A17,">"&Minimum)-COUNTIF(A7:A17,">"&Maximum)

"NCINDASUN" wrote:

> 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 "between" function.
>
> Can anyone help? Thanks

JMB wrote...
>where minimum and maximum are named cell references
>
>=COUNTIF(A7:A17,">"&Minimum)-COUNTIF(A7:A17,">"&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,">="&Min)-COUNTIF(Rng,">"&Max)

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

=COUNTIF(Rng,">"&Min)-COUNTIF(Rng,">="&Max)

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

=COUNTIF(Rng,">="&Min)-COUNTIF(Rng,">="&Max)

=IF(A1>A2,"LARGE",IF(A1<A3,"SMALL","OK"))

The numeric version for an overview:
=IF(A1>1000,1,IF(A1<1000,-1,0))

"Harlan Grove" wrote:

> JMB wrote...
> >where minimum and maximum are named cell references
> >
> >=COUNTIF(A7:A17,">"&Minimum)-COUNTIF(A7:A17,">"&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,">="&Min)-COUNTIF(Rng,">"&Max)
>
> If the OP wants to exclude both min and max bounds from the count,
>
> =COUNTIF(Rng,">"&Min)-COUNTIF(Rng,">="&Max)
>
> To include items equal to min but exclude items equal to max,
>
> =COUNTIF(Rng,">="&Min)-COUNTIF(Rng,">="&Max)
>
>

ufo_pilot wrote...
....
>The numeric version for an overview:
>=IF(A1>1000,1,IF(A1<1000,-1,0))
....

So clever! But why not

=SIGN(A1-1000)

? Or don't you like short and simple?