Counting frequency of numbers in a column

  • Thread starter Thread starter wabbleknee
  • Start date Start date
W

wabbleknee

I have a column of numbers in the range of 000 thru 999. I want to count
the number of occurrences that a number repeats. i.e. 1, 2, 3, 4, 3,
421, 421, 421. Expected output would be the number 3 would have a count of
2, 421 would be 3 etc. No need to count single occurrences. Tx
 
Suppose your numbers are in column A, with a heading in A1 like
"Number" (you must have a header for this to work, so insert a row at
the top if you don't have one). Highlight all the data including the
heading, then click on Data | Filter | Advanced Filter (XL2003 or
earlier) or Data tab | Advanced (XL2007 or later). In the dialogue box
you should click on Copy to another location, and in the Copy To box
you should put in the cell where you would like the data to be
filtered to (eg C1), click Unique Records only, then OK. This will
have extracted the unique values from your list and put them in column
C, with your header in C1 - you might like to sort this list.

Then in D2 you can have this formula:

=COUNTIF(A:A,C2)

and then copy this down to give you a count of each of your unique
values.

Hope this helps.

Pete
 
Thanks Pete, that works as expected. You answered another question I had a
while back, the reward was a beer, NOW your balance is +2 Beers ;o)

"Pete_UK" wrote in message

Suppose your numbers are in column A, with a heading in A1 like
"Number" (you must have a header for this to work, so insert a row at
the top if you don't have one). Highlight all the data including the
heading, then click on Data | Filter | Advanced Filter (XL2003 or
earlier) or Data tab | Advanced (XL2007 or later). In the dialogue box
you should click on Copy to another location, and in the Copy To box
you should put in the cell where you would like the data to be
filtered to (eg C1), click Unique Records only, then OK. This will
have extracted the unique values from your list and put them in column
C, with your header in C1 - you might like to sort this list.

Then in D2 you can have this formula:

=COUNTIF(A:A,C2)

and then copy this down to give you a count of each of your unique
values.

Hope this helps.

Pete
 
You're welcome - thanks for feeding back.

I'm not sure how I can cash in all these virtual beers I've been
offered over the years !! <bg>

Pete
 
Back
Top