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.
This document, titled "Excel - Find occurences of same value in a column," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).