Function or Formula needed for Car Show results 1st, 2nd, 3rd place

**MacArtist** · 02-23-2014, 01:10 PM

Hello,
I am new to the Forum and a rookie with spreadsheets. I have been using both Excel and Google spreadsheets. I have been trying to figure out how we could tally up all the judging results in each class and figure out who received the most votes, the 2nd most votes and the 3rd most votes. I would like to do this for each car class in separate sheets. We need just a simple formula that can tell us in a column which number (show car number) came up the most and which came up 2nd most and 3rd most. I hope I'm making sense.

For example:
Car Number
3
14
7
6
9
35
150
7
7
35
35
7
14
7
End Results: #7 is 1st, #35 is 2nd, #14 is 3rd

Thank you for your time!

**etaf** · 02-23-2014, 01:24 PM

you should be able to use rank to rank each car

http://www.cpearson.com/excel/Rank.aspx

so you could use a countif()
to get the number of times a car number appears
and then rank the result

a pivot table would work well

see attached

**Tony Valko** · 02-23-2014, 03:18 PM

Try this...

Data Range

	A	B	C	D
1	Car Number	-----	Place	Car Number
2	3		1st	7
3	6		2nd	35
4	7		3rd	14
5	7
6	7
7	7
8	7
9	9
10	14
11	14
12	35
13	35
14	35
15	150

This array formula** entered in D2 and copied down:

=MODE(IF(ISNA(MATCH(A$2:A$15,D$1:D1,0)),A$2:A$15))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

I sorted column A just to make it easy to see what the results should be.