count number of occurances between 2 values
 

I am trying to count how many cells contain a number within a certain range
eg number of cells in a column that are between 1000 and 2000.
I thought i could write it like =countif (A1:A4, 1000 <2000) doesn't work
for me
A
1000
4502
1203
2308

count number of occurances between 2 values
 

between 1000 and 2000.

Just so we understand...*between will exclude* from the count both 1000 and
2000.

=COUNTIF(A1:A4,"1000")-COUNTIF(A1:A4,"=2000")

--
Biff
Microsoft Excel MVP


"Peters" wrote in message
...
I am trying to count how many cells contain a number within a certain range
eg number of cells in a column that are between 1000 and 2000.
I thought i could write it like =countif (A1:A4, 1000 <2000) doesn't work
for me
A
1000
4502
1203
2308

count number of occurances between 2 values
 

Try this: =SUMPRODUCT((A1:A41000)*(A1:A4<2000))

Success? Express it here, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Peters" wrote:
I am trying to count how many cells contain a number within a certain range
eg number of cells in a column that are between 1000 and 2000.
I thought i could write it like =countif (A1:A4, 1000 <2000) doesn't work
for me
A
1000
4502
1203
2308

count number of occurances between 2 values
 

Peters wrote:
I am trying to count how many cells contain a number within a certain range
eg number of cells in a column that are between 1000 and 2000.
I thought i could write it like =countif (A1:A4, 1000 <2000) doesn't work
for me
A
1000
4502
1203
2308


Try
=COUNTIF(A1:A4,"1000")-COUNTIF(A1:A4,"=2000")

N.B. "Between" usually implies an inclusive test, i.e., "1000 is between
1000 and 2000" is a true statement. If that is the case for you, try
this instead:
=COUNTIF(A1:A4,"=1000")-COUNTIF(A1:A4,"2000")