PC Review


Reply
Thread Tools Rate Thread

Counting numbers greater or less than a number

 
 
=?Utf-8?B?a2lwcGVycw==?=
Guest
Posts: n/a
 
      2nd Jul 2007
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
 
Reply With Quote
 
 
 
 
=?Utf-8?B?TWlrZSBI?=
Guest
Posts: n/a
 
      2nd Jul 2007
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

 
Reply With Quote
 
=?Utf-8?B?TWlrZSBI?=
Guest
Posts: n/a
 
      2nd Jul 2007
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

 
Reply With Quote
 
=?Utf-8?B?TWF4?=
Guest
Posts: n/a
 
      2nd Jul 2007
"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:A2000>5)*(A1:A2000<10))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
counting the number of attributes/numbers in a cell Mortir Microsoft Excel Worksheet Functions 3 9th Jan 2009 10:08 AM
Counting number of unknown numbers Jan Kronsell Microsoft Excel Worksheet Functions 4 23rd Nov 2008 11:21 AM
Counting number of pay period dates between 2 numbers janplan Microsoft Excel Worksheet Functions 17 10th Aug 2008 07:46 AM
counting number of times value is greater than previous week =?Utf-8?B?dGVyZWFzYWp3?= Microsoft Excel Worksheet Functions 4 19th Nov 2005 02:50 PM
Counting numbers withing a mixed text/number cell Jared Croft Microsoft Excel Discussion 3 18th Jan 2004 03:17 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:13 PM.