counting values in a column
in [Functions]
|
From: Jerry on 2 Mar 2010 10:08 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.
From: Stefi on 2 Mar 2010 10:24 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: > 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.
From: Jerry on 2 Mar 2010 10:38 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. "Stefi" wrote: > 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: > > > 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.
From: pmartglass on 2 Mar 2010 10:48 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 "Jerry" wrote: > 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.
From: Glenn on 2 Mar 2010 11:02
Jerry wrote: > 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. One way: B1=COUNTIF(A:A,"<30") B2=COUNTIF(A:A,"<60")-B1 B3=COUNT(A:A)-SUM(B1:B2) |