# How to show the frequency amount

1. ## How to show the frequency amount

Hello!

I have a list of numbers (1-60) with some duplicates and I want to organize them so that is shows the frequency that the number appears next to the list of numbers without duplicates.
So for example:

3
4
5
5
4
6

I would want it to show like this:

3 1
4 2
5 2
6 1

I was told to use pivot table but I'm not sure how to go about it.

Many thanks,
Cheers!

2. ## Re: How to show the frequency amount

Let's say that your list of duplicates is in range A2:A100 and your unique numbers are in C2:C10. in D2 and down: =COUNTIF(\$A\$2:\$A\$100,\$C2)
Or set a pivot: Select the data - Insert - Pivot table - add the numbers to Rows and to values and set it as Count.

3. ## Re: How to show the frequency amount

In Excel you have dedicated function for Frequency - not that strange it's called FREQUENCY.

If you want to use full power of this function - it is a bit tricky, because it shall be committed as array formula*

*...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

So I wrote 1...60 in column D, then selected cells E1:E60 and having them all selected (not just one) I wrote a formula:
Formula:
`Please Login or Register  to view this content.`

and committed it with Ctrl+Shift+Enter

There are currently 1 users browsing this thread. (0 members and 1 guests)