Counting numbers greater or less than a number
 

How can I count the total number of cells (around 2000 rows worth) that are
less than or equal to a set of numbers (i.e. 5 and <10)?

Have been using the COUNT and COUNTIF functions and summing them up but this
obviously takes all cases greater than 5 and ALL cases below 11 (and hence
below 5) which distorts the result??

Any ideas welcome!!

Cheers

Counting numbers greater or less than a number
 

You do it with 2 countifs and subtraction:-


=COUNTIF(A1:A10000,"5")-COUNTIF(A1:A10000,"11")

Mike

"kippers" wrote:

How can I count the total number of cells (around 2000 rows worth) that are
less than or equal to a set of numbers (i.e. 5 and <10)?

Have been using the COUNT and COUNTIF functions and summing them up but this
obviously takes all cases greater than 5 and ALL cases below 11 (and hence
below 5) which distorts the result??

Any ideas welcome!!

Cheers

Counting numbers greater or less than a number
 

Oops,

I missed the equals bit so:-

=COUNTIF(A1:A10000,"=5")-COUNTIF(A1:A10000,"=11")

Mike


"Mike H" wrote:

You do it with 2 countifs and subtraction:-


=COUNTIF(A1:A10000,"5")-COUNTIF(A1:A10000,"11")

Mike

"kippers" wrote:

How can I count the total number of cells (around 2000 rows worth) that are
less than or equal to a set of numbers (i.e. 5 and <10)?

Have been using the COUNT and COUNTIF functions and summing them up but this
obviously takes all cases greater than 5 and ALL cases below 11 (and hence
below 5) which distorts the result??

Any ideas welcome!!

Cheers

Counting numbers greater or less than a number
 

"kippers" wrote:
How can I count the total number of cells (around 2000 rows worth) that are
less than or equal to a set of numbers (i.e. 5 and <10)?

Another way

Assuming source data within A1:A2000

Placed in say, B1:
=SUMPRODUCT((A1:A20005)*(A1:A2000<10))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---