count number of instances a number occurs

[mark (plymouth)]

Hi All,
i am trying, without success to creat a formula to do the following:
i have a series of values in a column and want to show how manay time a
number occurs within a range from the list.
i.e. in my list i have 12,17,23,24,25,23,24,22,26,43,32,32,33,44,53
i then want to display the number of instance there is an occurance of:
<10, 10-20, 21-30, 31-40, 41-50, >51
I would appreciate any assitance you might offer.

Thanks

 

[Mike H]

Hi,

i think from these 2 examples you should be able to work out the other
formula.

=COUNTIF(A1:A20,"<10")
=COUNTIF(A1:A20,">=10")-COUNTIF(A1:A120,">20")


Mike

 

[Pete_UK]

Try it this way:

=COUNTIF($A$1:$A$15,"<10")

for the first one, assuming your data is in A1:A15. Then this for the
next one:

=COUNTIF($A$1:$A$15,">="&10) - COUNTIF($A$1:$A$15,">"&20)

You could then copy this formula down and just change the 10 and 20 to
21 and 30, then 31 and 40 etc to suit your ranges.

Hope this helps.

Pete

 

[Mike H]

I did of course mean
=COUNTIF(A1:A20,">=10")-COUNTIF(A1:A20,">20")

 

[mark (plymouth)]

Thanks very much, that works fine.

Hi,

i think from these 2 examples you should be able to work out the other
formula.

=COUNTIF(A1:A20,"<10")
=COUNTIF(A1:A20,">=10")-COUNTIF(A1:A120,">20")


Mike

 

[Teethless mama]

Assume your data in A1:A15
Create a Bin range
B1: 9
B2: 20
B3: 30
B4: 40
B5: 50
B6: 1000

Highlight blank cells C1:C6 then enter formula: =FREQUENCY(A1:A15,B1:B6)

ctrl+shift+enter, not just enter