Excel

[Solved/Closed]
Report
Posts
1
Registration date
Friday December 25, 2009
Status
Member
Last seen
December 25, 2009
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month