Please Help in Excel Range
 

i have Excel Sheet , like this samples given below in each cell
11
12
13
14
15
16
17
18
19
20

well i want that how want to count how many times is 1 repeated ,how many
times is 2 repeat n follow on...this is jsut small range as it is very big
sheet starting from 401 to 1000

regards

husain

Please Help in Excel Range
 

Do the numbers range from 1 to 100?
When you say "count how many times is 1 repeated ", you do NOT mean the
digit 1 as the 1 in 10, 11, 12 but the number 1 (no 1's in your example)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Husain Nagri" wrote in message
...
i have Excel Sheet , like this samples given below in each cell
11
12
13
14
15
16
17
18
19
20

well i want that how want to count how many times is 1 repeated ,how many
times is 2 repeat n follow on...this is jsut small range as it is very big
sheet starting from 401 to 1000

regards

husain

Please Help in Excel Range
 

=COUNTIF(A401:A1000,1)

Or, if you have the numbers 1, 2, etc in cells (e.g. K1, K2 etc) then

=COUNTIF($A$401:$A$1000,K1)

And copy down

HTH
Kostis Vezerides

On Oct 31, 4:28 pm, Husain Nagri
wrote:
i have Excel Sheet , like this samples given below in each cell
11
12
13
14
15
16
17
18
19
20

well i want that how want to count how many times is 1 repeated ,how many
times is 2 repeat n follow on...this is jsut small range as it is very big
sheet starting from 401 to 1000

regards

husain

Please Help in Excel Range
 

Well i mean...let say if its like this way
11
12
13
14
15

so how many times is digit 1 repeat...so in above example the answer would
be 6 times digit 1 is repeated...so pls show me how to do in whole range in
excel sheet
regards


"Bernard Liengme" wrote:

Do the numbers range from 1 to 100?
When you say "count how many times is 1 repeated ", you do NOT mean the
digit 1 as the 1 in 10, 11, 12 but the number 1 (no 1's in your example)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Husain Nagri" wrote in message
...
i have Excel Sheet , like this samples given below in each cell
11
12
13
14
15
16
17
18
19
20

well i want that how want to count how many times is 1 repeated ,how many
times is 2 repeat n follow on...this is jsut small range as it is very big
sheet starting from 401 to 1000

regards

husain

Please Help in Excel Range
 

Enter the number you want to count into A400, then try this:

=SUMPRODUCT(LEN(A401:A1000)-LEN(SUBSTITUTE(A401:A1000,A400,"")))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Husain Nagri" wrote in message
...

Well i mean...let say if its like this way
11
12
13
14
15

so how many times is digit 1 repeat...so in above example the answer would
be 6 times digit 1 is repeated...so pls show me how to do in whole range in
excel sheet
regards


"Bernard Liengme" wrote:

Do the numbers range from 1 to 100?
When you say "count how many times is 1 repeated ", you do NOT mean the
digit 1 as the 1 in 10, 11, 12 but the number 1 (no 1's in your example)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Husain Nagri" wrote in message
...
i have Excel Sheet , like this samples given below in each cell
11
12
13
14
15
16
17
18
19
20

well i want that how want to count how many times is 1 repeated ,how
many
times is 2 repeat n follow on...this is jsut small range as it is very
big
sheet starting from 401 to 1000

regards

husain

Please Help in Excel Range
 

Forgot that you may count a number containing more then a single digit.

Use this instead:

=SUMPRODUCT(LEN(A401:A1000)-LEN(SUBSTITUTE(A401:A1000,A400,"")))/LEN(A400)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"RagDyeR" wrote in message
...
Enter the number you want to count into A400, then try this:

=SUMPRODUCT(LEN(A401:A1000)-LEN(SUBSTITUTE(A401:A1000,A400,"")))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Husain Nagri" wrote in message
...

Well i mean...let say if its like this way
11
12
13
14
15

so how many times is digit 1 repeat...so in above example the answer would
be 6 times digit 1 is repeated...so pls show me how to do in whole range in
excel sheet
regards


"Bernard Liengme" wrote:

Do the numbers range from 1 to 100?
When you say "count how many times is 1 repeated ", you do NOT mean the
digit 1 as the 1 in 10, 11, 12 but the number 1 (no 1's in your example)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Husain Nagri" wrote in message
...
i have Excel Sheet , like this samples given below in each cell
11
12
13
14
15
16
17
18
19
20

well i want that how want to count how many times is 1 repeated ,how
many
times is 2 repeat n follow on...this is jsut small range as it is very
big
sheet starting from 401 to 1000

regards

husain