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

Between? Is there a way?
 

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

Between? Is there a way?
 

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)

Between? Is there a way?
 

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

The numeric version for an overview:
=IF(A11000,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)

Between? Is there a way?
 

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

So clever! But why not

=SIGN(A1-1000)

? Or don't you like short and simple?