Return Three Highest Categories

[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

 

[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

 

[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

 

[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