Excel Formula.

[GforceD]

So I am dumb and not at all mathmatically minded.

I am looking for a formula that takes a list of numbers ( or names ) like 1,1,1,1,2,2,2,3,3,3,3,3,4,5,5,...
and then displays the top 3 or so results and the count. ie 3 (5),1 (4) ,2 (3).

I just assumed it would be easy then I realised I have no clue what I am doing.

 

[Trust|Nobody]

Why don't you attach the workbook with your specific example?

 

[shearder]

Maybe create a column with a countif formula or a count of something specific, and then sort THAT column by descending?

 

[Xino_sam]

If your criteria are listed under column A, try the following???
=CONCATENATE(RANK(A1,$A$1:$A$15,1)," (",COUNTIF($A$1:$A$15,A1),")")

A1:A15 the range of your data, this you can change

 

[shearder]

If your criteria are listed under column A, try the following???
=CONCATENATE(RANK(A1,$A$1:$A$15,1)," (",COUNTIF($A$1:$A$15,A1),")")

A1:A15 the range of your data, this you can change

Why concatenate?

 

[GforceD]

Why don't you attach the workbook with your specific example?

because it doesn't exist yet. and essentialy its would just a whole lot of numbers like my example.

 

[shearder]

because it doesn't exist yet. and essentialy its would just a whole lot of numbers like my example.

I suggest a column with a count of each value sorted in descending order

 

[GforceD]

If your criteria are listed under column A, try the following???
=CONCATENATE(RANK(A1,$A$1:$A$15,1)," (",COUNTIF($A$1:$A$15,A1),")")

A1:A15 the range of your data, this you can change

I hate you! damn it looks so easy, and concatenate to make it all pretty in one cell.

I was trying to workout formulas to list and compare and display and meanwhile its as simple and RANK.
Thanks.

I need to play in excel more.

 

[Xino_sam]

Why concatenate?

Concatenate to combine all data in one cell and to add the brackets "( )", you could also use the "&" to combine more than one cell's data into one cell

 

[shearder]

Concatenate to combine all data in one cell and to add the brackets "( )", you could also use the "&" to combine more than one cell's data into one cell

Buy not sure he was ts all in one cell BUT if so then YES

 

[shearder]

Gosh what I wrote was not what I saw lol meant of one cell is what he wants then yes hahahaha

 

[GforceD]

Gosh what I wrote was not what I saw lol meant of one cell is what he wants then yes hahahaha

I got the gist.

 

[RedViking]

Ag this lady you know....

@KT-B

 

[I.am.Sam]

just do a pivot

it will do a count

and you can filter top 3

https://www.contextures.com/excel-pivot-table-filters-top10.html

 

[I.am.Sam]

if your data is all on one line you need to do a text to column

then transpose and then do a pivot

 

[GforceD]

I am doing something wrong, but i need to sit down and try it out, Rank is not giving me a result I understand.

I'll have to read up on pivot tables.

 

[Milano]

Select column > Data > subtotals > count
Then you can manipulate subtotals as you want thereafter

 

[I.am.Sam]

I am doing something wrong, but i need to sit down and try it out, Rank is not giving me a result I understand.

I'll have to read up on pivot tables.

highlight the data

click on insert tab

click pivot table