# Excel

Solved/Closed
WILL511958 Posts 1 Registration date Friday December 25, 2009 Status Member Last seen December 25, 2009 - Dec 25, 2009 at 09:20 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Dec 27, 2009 at 08:00 PM
Hello,
I need some help. I would like to find out how many occurences of the same number occur in a column with in excel.
Eample: 1111155555555666666666666 (vertical column, althought not depicted here
1's=50
5's=8
6's=12
I have merged multiple colums into a single column totaling 5000. I have sorted them numerically but there are just to many to manually count to get the info i need.

Is there a formula I can enter that will count the like number instances thar occur in the column?

Thanks
Will
Related:

## 1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 27, 2009 at 08:00 PM
Is 1111155555555666666666666 in one cells or
is it a1 1 a2 1 a3 1 etc A6 5 a7 5 and go on

if the numbers are different cells in a column and if the total no,.of rows is 5000
try this formula

=countif(a1:a5000,"1")

note 1 should be within quotes..

In the whole string or number in one cell then use this (supposing the cell is E1)

=LEN(E1)-LEN(SUBSTITUTE(E1,"1",""))

This will give number 1s in that cell.