which number repeats and how many times?

[excelNewbie22]

hi,
is there's a formula for finding which number in a range, say a1:a1000, repeat itself the most,
and how many times?

example: (the actual data contains numbers only but longer)
1
2
1
2
1
3
4
5
6
2
3
1
3
2
2
2
1

 

[etaf]

if they are all numbers - then MODE() and countif()

Book1
	A	B	C	D
1			Mode	Count
2	1		2	6
3	2
4	1
5	2
6	1
7	3
8	4
9	5
10	6
11	2
12	3
13	1
14	3
15	2
16	2
17	2
18	1
19
Sheet1

Cell Formulas
Range	Formula
C2	C2	=MODE(A2:A18)
D2	D2	=COUNTIF(A2:A18,C2)

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1	1	Number	Count
2	2	2	6
3	1
4	2
5	1
6	3
7	4
8	5
9	6
10	2
11	3
12	1
13	3
14	2
15	2
16	2
17	7
18	1
19	3
20	3
21
Data

Cell Formulas
Range	Formula
B2	B2	=MODE(A1:A1000)
C2	C2	=COUNTIFS(A:A,B2)

 

[excelNewbie22]

thank you both it doesn't work with my data,

test.xlsb
	AW
1	#N/A
2	11011111011111101
3	11111111111111111
4	01111101111110111
5	11110111110111010
6	11011111011111101
7	01011111111111111
8	11010110101011111
9	11111101111111111
10	11011111011111101
11	11011111110010010
12	11111111001111111
13	11111111111111111
14	11011111011111101
15	11111111110111111
16	01011010110111110
17	11011111111011011
18	11100111101110111
19	11111111111111111
20	11101011111111111
test

Cell Formulas
Range	Formula
AW1	AW1	=MODE(AW2:AW21)

 

[Fluff]

That's because that is nothing like what you originally showed.
With over 90 threads to your name, you should know by now that if you post data that is nothing like the real data, any solution is not going to work, so why oh why do you still do it?

 

[excelNewbie22]

i'm sorry, usually i'm trying to be precise as i can, and usually i'm posting data for example,
sometimes like now i didn't thought the need, and i was mistaken

 

[ekrause]

Copying your data directly and using the same function works on my end. My first thought is you may have a formatting issue

be precise as i can, and usually i'm posting data for example,
sometimes like now i didn't thought the

 

[Fluff]

Copying your data directly and using the same function works on my end.

That's because xl will interpret the values as number & convert them, but the formula will not work with text, which is what the OP has.

 

[ekrause]

2 options for you, @excelNewbie22

Add a helper column with =[Cell]*1, Use =Mode(b1:b1000) off this new number column.

Create a copy -> Remove Dups -> Countifs formula -> Sort Process. This is a worse solution but available to you.

 

[Fluff]

Try

=INDEX(AW2:AW20,MODE(MATCH(AW2:AW20,AW2:AW20,0)))