Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting values in a column
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting values in a column
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting values in a column
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting values in a column
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting values in a column
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) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting values in a column
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 "pmartglass" wrote: 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting values in a column
Then you want Stefi's solution.
Regards, Fred "Jerry" wrote in message ... 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 "pmartglass" wrote: 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting values in a column
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 D2:D4 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. -- Biff Microsoft Excel MVP "Jerry" wrote in message ... 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 "pmartglass" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting values in column B in condition of A | Excel Worksheet Functions | |||
Counting unique values in column | Excel Discussion (Misc queries) | |||
Counting different values from same column | Excel Discussion (Misc queries) | |||
Counting two or more values from same column | Excel Discussion (Misc queries) | |||
Counting values in every other column | Excel Worksheet Functions |