Home |
Search |
Today's Posts |
#1
|
|||
|
|||
CUTOFF
Dear all;
I have a small problem with this function, A column has numerical numbers, I want to count the same numbers, please look to my example A 1 1 1 2 2 1 3 3 3 2 1 1 3 2 2 The results in this form 1=3 rows 2=3 rows 3=2 rows thank you |
#2
|
|||
|
|||
Hi Ahmad,
Not sure if I'm clear on what you want as I counted 6 rows with the number 1. The others weren't right either. However, I think what you need is this formula (1 for each number you want to count). =COUNTIF($A$1:$A$15,1) =COUNTIF($A$1:$A$15,2) =COUNTIF($A$1:$A$15,3) (Assuming your data is in A1 to A15.) That will give you the count but if you want it to appear as 1=6 Rows, etc., you will need to format each cell as Custom using the following as appropriate: 1=0 "rows". 2=0 "rows" 3=0 "rows" Rob "Ahmad" wrote in message om... Dear all; I have a small problem with this function, A column has numerical numbers, I want to count the same numbers, please look to my example A 1 1 1 2 2 1 3 3 3 2 1 1 3 2 2 The results in this form 1=3 rows 2=3 rows 3=2 rows thank you |
#3
|
|||
|
|||
If you want to count how many groups of 1's, 2's, etc. there a
Start the numbers in row 2, or below (use row 1 for headings, or leave it blank) With the first number in A2, enter the following formula in B2: =IF(A2=OFFSET(A2,-1,0),0,1) Copy the formula down to the last row of data In another column, enter the list of unique numbers. For example in D2:D4: 1 2 3 In the adjacent column, enter a formula that refers to the long list of numbers, and to the unique list. For example, in E2: =SUMIF($A$2:$A$16,D2,$B$2:$B$16) Copy the formula down to the last row of data Ahmad wrote: Dear all; I have a small problem with this function, A column has numerical numbers, I want to count the same numbers, please look to my example A 1 1 1 2 2 1 3 3 3 2 1 1 3 2 2 The results in this form 1=3 rows 2=3 rows 3=2 rows thank you -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
"Rob" <NA wrote in message ...
Hi Ahmad, Not sure if I'm clear on what you want as I counted 6 rows with the number 1. The others weren't right either. However, I think what you need is this formula (1 for each number you want to count). =COUNTIF($A$1:$A$15,1) =COUNTIF($A$1:$A$15,2) =COUNTIF($A$1:$A$15,3) (Assuming your data is in A1 to A15.) That will give you the count but if you want it to appear as 1=6 Rows, etc., you will need to format each cell as Custom using the following as appropriate: 1=0 "rows". 2=0 "rows" 3=0 "rows" Rob "Ahmad" wrote in message om... Dear all; I have a small problem with this function, A column has numerical numbers, I want to count the same numbers, please look to my example A 1 1 1 2 2 1 3 3 3 2 1 1 3 2 2 The results in this form 1=3 rows 2=3 rows 3=2 rows thank you Dear Rob; My data A1:A15, my question about how many rows have the same numbers for example A1:A3 has the same number (1) we say this is one row becuase its continouse, A6 has number 1 this is one row and A11:A12 has the same numbers (1) my results should be number one has 3 row. you can apply this method to number 2 and 3 |
#5
|
|||
|
|||
Dear Debra;
Thank you for your help you made as you need. Good luck Debra Dalgleish wrote in message ... If you want to count how many groups of 1's, 2's, etc. there a Start the numbers in row 2, or below (use row 1 for headings, or leave it blank) With the first number in A2, enter the following formula in B2: =IF(A2=OFFSET(A2,-1,0),0,1) Copy the formula down to the last row of data In another column, enter the list of unique numbers. For example in D2:D4: 1 2 3 In the adjacent column, enter a formula that refers to the long list of numbers, and to the unique list. For example, in E2: =SUMIF($A$2:$A$16,D2,$B$2:$B$16) Copy the formula down to the last row of data Ahmad wrote: Dear all; I have a small problem with this function, A column has numerical numbers, I want to count the same numbers, please look to my example A 1 1 1 2 2 1 3 3 3 2 1 1 3 2 2 The results in this form 1=3 rows 2=3 rows 3=2 rows thank you |
#6
|
|||
|
|||
You're welcome. Thanks for letting me know that it helped.
Ahmad wrote: Dear Debra; Thank you for your help you made as you need. Good luck Debra Dalgleish wrote in message ... If you want to count how many groups of 1's, 2's, etc. there a Start the numbers in row 2, or below (use row 1 for headings, or leave it blank) With the first number in A2, enter the following formula in B2: =IF(A2=OFFSET(A2,-1,0),0,1) Copy the formula down to the last row of data In another column, enter the list of unique numbers. For example in D2:D4: 1 2 3 In the adjacent column, enter a formula that refers to the long list of numbers, and to the unique list. For example, in E2: =SUMIF($A$2:$A$16,D2,$B$2:$B$16) Copy the formula down to the last row of data Ahmad wrote: Dear all; I have a small problem with this function, A column has numerical numbers, I want to count the same numbers, please look to my example A 1 1 1 2 2 1 3 3 3 2 1 1 3 2 2 The results in this form 1=3 rows 2=3 rows 3=2 rows thank you -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|