# Excel - Find occurences of same value in a column

January 2017

## Issue

I need some help. I would like to find out how many occurrences of the same number occur in a column with in excel.
Example: 1111155555555666666666666 (vertical column, although not depicted here

`1's=50 5's=8 6's=12 `

I have merged multiple columns into a single column totalling 5000. I have sorted them numerically but there are just too many to manually count to get the info i need.

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

## Solution

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.

## Note

Thanks to venkat1926 for this tip on the forum.

## Related

Published by aakai1056. Latest update on October 20, 2016 at 10:14 AM by owilson.