Counting the number of occurrences a number appears in a range

**greenmal** · 10-06-2014, 11:35 AM

Hi All--

I feel like this topic may have been cover sooo many times, but I can't seem to find a solution, so here goes.

I have a column of numbers ranging from 0-77 (over 4500 rows of data) and I am looking to determine the number of occurrences each number appears in my range.

I have been attempting to use a 'countif' formula, but it doesn't seem to be working.

Any help you all can offer would be appreciated. Thanks in advance!

**AlKey** · 10-06-2014, 11:50 AM

Assuming there are numbers in column A

use this formula in B2 and copy it down until you see blanks

=COUNTIF(A:A,IF(ROWS($B$2:B2)>78,"",ROWS($B$2:B2)-1))

	A	B
1	Numbers	Count
2	1	0
3	5	2
4	1	0
5	6	0
6	5	1
7	4	2
8	25	1
9	25	0
10	12	0

**Sam Capricci** · 10-06-2014, 11:58 AM

if i were doing this, assuming my data (4500 rows) begins in A1 and continues through A4500, in b1 i would put 0, then b2 1, b3 2, then having established the series, drag down for 78 rows autopopulating the rest. then use =COUNTIF(A:A,B1) and drag down.
if this doesn't work, then there may be a hidden space in the data. So you can copy the data set to another location then use remove duplicates and this will give you your 78 data points, then refer the countif to this set - lets say you put this set in C1, =COUNTIF(A:A,C1).