Please Help in Excel Range

[Guest]

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

 

[Bernard Liengme]

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

 

[vezerid]

=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

 

[Guest]

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

 

[RagDyeR]

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!
=====================================================



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

 

[RagDyeR]

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!
=====================================================

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!
=====================================================



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