Return Three Highest Categories

E

Eli

I would like a formula that returns the three highest categories within a
list. I think an example would be best:

A B
1 Animal Count
2 Dog 3
3 Cow 1
4 Dog 2
5 Cat 5
6 Sheep 2
7 Cat 2
8 Chicken 4

From this list I want to know the top 3 animals, thus the correct answer
would be:

A B
1 Animal Count
2 Cat 7
3 Dog 5
4 Chicken 4

Is there a nifty little (or big) forumla to give me the results in column A?
For what it is worth I am trying to avoid array formulas but if that is the
only way to do it I will live with it.

Thank you,
Eli
 
D

dhstein

With helper columns
Formula in D1 (copied down) =SUMIF($B$1:$B$7,$B1,$C$1:$C$7)
Formula in E1 (copied down) =B1
Formula in F1 (copied to F2 and F3) =LARGE($D$1:$D$7,1)
Formula in G1 (copied to G2) =COUNTIF($D$1:$D$7,F1)
Formula in H1 (copied to H2 and H3) =VLOOKUP(F1,$D$1:$E$7,2,FALSE)
Formula in I1:I4 =F1
 
M

Max

Another play is to pivot your source table, placing "Animal" in the ROW area
and "Count" in the DATA area (set to Sum). Then just select the header
"Animal" in the pivot, click the "PivotTable" dropdown > Sort and Top 10 (in
the pivot toolbar), and set the autosort options as desired, viz.: Autosort >
Descending, using field: Sum of Count. Up and running in a matter of seconds
...
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
 
E

Eli

Hi dhstein,

Thanks for the insight, this is exactly what I wanted. I did have to make a
few changes to make this work.

With helper columns
Formula in D1 (copied down) =SUMIF($A$1:$A$7,$A1,$B$1:$B$7)
Formula in E1 (copied down) =A1
Formula in F1 =LARGE($D$1:$D$7,1)
Formula in F2 =LARGE($D$1:$D$7,G1+1)
Formula in F3 =LARGE($D$1:$D$7,sum(G1:G2)+1)
Formula in G1 (copied to G2) =COUNTIF($D$1:$D$7,F1)
Formula in H1 (copied to H2 and H3) =VLOOKUP(F1,$D$1:$E$7,2,FALSE)
Formula in I1:I3 =F1

Cheers,
Eli
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top