counting values in a column

[Jerry]

I have different values in a a column that I need to count.
Column A: 2,25,45,52,25,30,2,45,80,60
and I want to count values as follows
<30 = 4

30 and <60 = 4
60 = 2

I have tried countif and sumproduct but it doesn't work correctly. Your
assistance is greatly appreciated.

 

[Stefi]

Set up a table in column B1:B4 like this:

0
29
59
999

where 999 represents a number which is surely higher than the highest value
in column A,

select range C1:C4 and enter this formula:
=FREQUENCY($A$2:$A$11,$B$1:$B$4)
and confirm it with Ctrl+Shift+Enter (it's an array formula).



--
Regards!
Stefi



„Jerry†ezt írta:

 

[Jerry]

I can not do that because the other information is already sorted and if i
change my sorting will take me over two hours readjusting all other columns.

 

[pmartglass]

please explain what is not working correctly
the only thing that I see that may be a problem is how you are
handling situations like = 30 or = 60
these situations will be excluded from your logic
if this is causing your problem you may just need to modify your starting or
ending points accordingly

good luck

 

[Glenn]

I have different values in a a column that I need to count.
Column A: 2,25,45,52,25,30,2,45,80,60
and I want to count values as follows
<30 = 4
I have tried countif and sumproduct but it doesn't work correctly. Your
assistance is greatly appreciated.

One way:

B1=COUNTIF(A:A,"<30")

B2=COUNTIF(A:A,"<60")-B1

B3=COUNT(A:A)-SUM(B1:B2)

 

[Jerry]

I put =countif(an2:an240,"<31") and it works correctly for these set of
values or anything greater, in between values I have to do
=countif(an2:an40,"<61")-whatever value I had before so if I have to
breakdown into more groups i have to keep adding and substracting prior
values so that my in between values are a pain in the neck to keep of track

 

[Fred Smith]

Then you want Stefi's solution.

Regards,
Fred

I put =countif(an2:an240,"<31") and it works correctly for these set of
values or anything greater, in between values I have to do
=countif(an2:an40,"<61")-whatever value I had before so if I have to
breakdown into more groups i have to keep adding and substracting prior
values so that my in between values are a pain in the neck to keep of
track

 

[T. Valko]

I think you're not understanding what pmartglass pointed out.

You posted these conditions:

<30 = 4

30 and <60 = 4
60 = 2

Less than 30
Greater than 30 and less than 60
Greater than 60

So, with those intervals you're not counting 30 or 60.

I'd use Stefi's suggestion but change the bins.

Let's assume your data is in the range A2:A11.

List the bins in C2:C4 -

C2 = 29
C3 = 59
C4 = >59

Those are the bins (conditions ) you need based on your posted results:

<30 = 4

30 and <60 = 4
60 = 2

Then, select the range D24
Type this formula into the *formula bar*:

=FREQUENCY(A2:A11,C2:C3)

Do not hit Enter. Instead hold down both the CTRL key and the SHIFT key then
hit Enter.