Count numbers.

[bjartmare]

Hi.

I have a list in Excel like:

10
20
10
24
25
30
10 etc. (Hundreds of numbers)

I need to count how often each numbers comes up.
But first I need Excel to make me a list of the numbers.
30
25
24
20
10

And then I would use COUNTIF(A1:150;B1)

All I can think of is the "Large" function but it's not exactly what I need. The numbers are changing from day to day so I would prefer some function for this instead of doing it manually.

[BadlySpelledBuoy]

Why not use a Pivot Table?

[pjwhitfield]

I know someone will have a formula for this however in its absence, the following Sub will do what you're after (you'll need to tidy it to find last row etc as this is just rough).

Please Login or Register  to view this content.

[FlameRetired]

Assuming your list of numbers is in column A put this in column B and fill down as far as you find adequate:

Please Login or Register  to view this content.

Then in column C this:

Please Login or Register  to view this content.

and fill down as far as adequate.

Or since your number list is changing I would suggest you create a Dynamic Named Range in Name Manager. I would suggest:

Please Login or Register  to view this content.

in the Refers to text box.
I chose the arbitrary name -- OrignalNums -- and put it in the Name text box.

Then your formulas would be in column B:

Please Login or Register  to view this content.

this would extract unique numbers and sort them in descending order.

Then in column C:

Please Login or Register  to view this content.

to get a count of those unique numbers.
Example file attached.

Does this do what you want?

[bjartmare]

Does this do what you want?

Yes, thank you. This is exactly what I needed. Thank you.

[FDibbins]

You could also use this for the unique values list...
=IFERROR(INDEX($B$4:$B$1000,MATCH(0,INDEX(COUNTIF($E$4:E4,$B$4:$B$1000),),0)),"")

[newdoverman]

If you know the range for the values that you want a count of, you can use the FREQUENCY formula to give a count of each value that is found within that range.

My example makes the range of possible numbers 1 to 85 which I entered in column C. These values can be entered using the SERIES function of Excel. (Fill, Series) The values to be counted are in A2 to A100. The formula is an array formula which is entered by Ctrl + Shift + Enter.

Select D2 to D86 and enter this formula.

Please Login or Register  to view this content.

COUNTIF can also be used: (this is a regular formula entered normally)

Please Login or Register  to view this content.

If you want to extract the unique values from the data before counting use this ARRAY formula. I entered the formula in G2 and copied down. If you use a different column, change the $g$1:G1 to the column that you want to use. Change the $G$1 and G1 to 1 row above the starting row you have.

Please Login or Register  to view this content.

Then use the COUNTIF or FREQUENCY formula to count the occurrences.

The enclosed file has examples of these formulae.

This is by no means all the ways to do this.

[FlameRetired]

Yes, thank you. This is exactly what I needed. Thank you.

You're welcome. Glad it helped. And thanks for the rep!