Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
bimseun
 
Posts: n/a
Default COUNTIF for range of numbers

I hav got the following numbers:
A
45
54
12
32
55
78
65
45
I want to use the count how many of this is within the ranges
0-40
41-80
Please can someone advice on how to wtrite the function.
bimseun
11-20

  #2   Report Post  
Posted to microsoft.public.excel.misc
JE McGimpsey
 
Posts: n/a
Default COUNTIF for range of numbers

One way:

0-40:

=COUNTIF(A:A,"<=40") - COUNTIF(A:A, "<0")

41-80:

=COUNTIF(A:A,"<=80") - COUNTIF(A:A, "<=40")

Alternatively:

0-40:

=SUMPRODUCT(--(A1:A100<=40),--(A1:A100=0))

41-80:

=SUMPRODUCT(--(A1:A100<=80),--(A1:A10040))


In article ,
"bimseun" wrote:

I hav got the following numbers:
A
45
54
12
32
55
78
65
45
I want to use the count how many of this is within the ranges
0-40
41-80
Please can someone advice on how to wtrite the function.
bimseun
11-20

  #3   Report Post  
Posted to microsoft.public.excel.misc
daddylonglegs
 
Posts: n/a
Default COUNTIF for range of numbers


If your numbers are all greater than or equal to zero and in range
A1:A10

=COUNTIF(A1:A10,"<=40")

If the above formula is in B1 then for 41-80

=COUNTIF(A1:A10,"<=80")-B1


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=504465

  #4   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default COUNTIF for range of numbers

On Tue, 24 Jan 2006 06:13:02 -0800, "bimseun"
wrote:

I hav got the following numbers:
A
45
54
12
32
55
78
65
45
I want to use the count how many of this is within the ranges
0-40
41-80
Please can someone advice on how to wtrite the function.
bimseun
11-20



=COUNTIF(rng,"=0") - COUNTIF(rng,"40")

However, depending on your application, you might be better served using the
FREQUENCY worksheet function.
--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Checking Winning Numbers in the Lottery. Ann Excel Discussion (Misc queries) 4 May 18th 05 10:55 AM
Using COUNTIF to find numbers within a range greater than the mean Lowkey Excel Worksheet Functions 2 May 17th 05 06:34 PM
How can I change positive numbers to negative, i.e. change 50 to - godwingi Excel Discussion (Misc queries) 5 February 28th 05 06:41 PM
Sorting when some numbers have a text suffix confused on the tundra Excel Discussion (Misc queries) 5 December 18th 04 11:19 PM
GET NON-ZERO RESULTS USING STDEV FOR CERTAIN NUMBERS Non-zero return for Stdev Excel Worksheet Functions 2 December 16th 04 09:44 AM


All times are GMT +1. The time now is 07:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"