excel formula Options

[katiecalf]

Not sure if this can be done and have tried a couple different formulas ( conditional )
I have a spreadsheet for a cow show that lists the exhibitors in column A
in columns c:v same row as name I have their point value according to how they place in each class
I need to pick out the top 6 placings from their row and put that total in end total column
is there a formula that will do that or am I wasting time

after each exhibitors top 6 are figured we then take top one from those numbers
didn't know how to attach file here but this gives general idea of what spreadsheet would look like like
under each class number 1r etc would be the box if they showed in that class to place their points awarded
thanks in advance!!


Heifer/dry cow class Points Cow class Points
Class # 1r r2 3r 4r 5r 6r 7r 17r 9r 10r 11r 12r 13r 14r 15r 16r total
placing 1st 2nd 3rd 4th 5th 6th 7th 8th 9th 10 1st 2nd 3rd 4th 5th 6th 7th 8th 9th 10 Points
points awarded 10 9 8 7 6 5 4 3 2 1 20 18 16 14 12 10 8 6 4 2
Exhibitor
1
2
3
4

[tos226]

It will help greatly if you could post a clear picture of sample data and lookup tables, as well as a clear picture of expected results for the sample data.
I suspect the enswer lies somewhere in nested-IF statement, sumIF, and perhaps Advanced filter.
If the lookup tables aren't clear, Match and Index functions in addition to Vlookup or Hlookup might come handy.

[tos226]

A little bit about part 1 of your post even though I still don't imagine your data
I think you will also need the rank function.

So if your points are in a row under those 20 or so "placing" headings, you may need to make a row for ranks under the points, where in each of the cells you'll have something like this:
Imagining further 2 rows with 10 in cell c14, and 14 in cell c15
points 10 9 8 7 6 etc
rank 14 13 11 10 8 etc

=RANK(C14,$C$14:$V$14,1) in colC
=RANK(D14,$C$14:$V$14,1) in colD
which you'll get by just dragging across the formula in c15.
Then you'll do SUMIF of those values in the points row which correspond to ranks above 14 or the highest rank-6.
But what if there are ties? What if you don't have 20 values filled? What if you have less than 6 values filled?

[tos226]

If you're still listening:
I may have come to grips with your design once pasted your message into excel.
It will actually be better if you put the ranks to the right of your points 20 columns C:V. For instance to X:AQ. Perhaps the total could be in between, in colW.
That will make it easier to calculate the max of the entries as well as expand/fill the range with formulas for ranks.
Also, while the columns C:V are fixed, row number have to move, therefore the formula should be
=RANK(C14,$C14:$V14,1) in colC where we drop $ before the row number in my previous version.
Then in column W you pick ranges above 14 and add up the points corresponding to those ranges, like this:
=SUMIF($X73:$AQ73,">14",$C73:$V73) and pull them down for all Exhibitor rows.
Finally, to declare the overall winner, get a max of column W
=MAX(W14:W114) assuming you got 100 or so Exhibitors.

I have a problem with picking 6 highest because ties could/will do funny things. But since this is only guessing this design, I leave it like that.

[katiecalf]

I know it was difficult to understand and I couldn't figure out how to attach the original spreadsheet.
I actually do not do so well with formulas in excel so get lost very easily
the simple way to explain what I was trying to do was this
each exhibitor has a row
across the row there are 20 different boxes exp: c:6 thru v6 with a possibility of a set amount of points in any of the columns
say for example a person showed 10 animals c6: would have value of 10
E6: would have value of 8
G6:2 J6: 8 F6:5 I6:20 k6: 18 L3:12 S6:20 T6:5
that would give you 10 placings and I need the top 6 placings from that row

some exhibitors will only have 4 animals entered then of course the total would be just the 4 placings

really don't think this can be done at any rate not how I would know how to do it
thanks for your help though