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