I need to be able to calculate the sum of, "the best of 6". Here is an
example:

Andy Harris 60 54 48 38 60 60 60 22 34

Total is 342

Would like a formula that would figure this out for me.

Thanks

=SUM(LARGE(A27:J27,{1,2,3,4,5,6}))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"okracerx" wrote in message
...
I need to be able to calculate the sum of, "the best of 6". Here is an
example:

Andy Harris 60 54 48 38 60 60 60 22 34

Total is 342

Would like a formula that would figure this out for me.

Thanks

=LARGE(B1:K1,1)+LARGE(B1:K1,2)+LARGE(B1:K1,3)+LARG E(B1:K1,4)+LARGE(B1:K1,5)+LARGE(B1:K1,6)

--
Gary''s Student - gsnu200908


"okracerx" wrote:

I need to be able to calculate the sum of, "the best of 6". Here is an
example:

Andy Harris 60 54 48 38 60 60 60 22 34

Total is 342

Would like a formula that would figure this out for me.

Thanks

Use this formula...

=SUM(LARGE(B1:J1,{1,2,3,4,5,6}))

Change the cell reference B1:J1 to your desired range, if required.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"okracerx" wrote:

I need to be able to calculate the sum of, "the best of 6". Here is an
example:

Andy Harris 60 54 48 38 60 60 60 22 34

Total is 342

Would like a formula that would figure this out for me.

Thanks

Thanks Don,
Works great, but what if one of my cells has text "DNF", value is zero. but
when i use the above forumla it give me a #num error. Thanks

"Don Guillett" wrote:

=SUM(LARGE(A27:J27,{1,2,3,4,5,6}))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"okracerx" wrote in message
...
I need to be able to calculate the sum of, "the best of 6". Here is an
example:

Andy Harris 60 54 48 38 60 60 60 22 34

Total is 342

Would like a formula that would figure this out for me.

Thanks

.

"DNF". Are you involved with racing? I was once an SCCA Formula Ford driver
and car importer.

That's because you have less than 6 number entries. You could increase the
range and put in some 0's
So use this instead. Be advised that you must enter using CSE
(control+shift+enter). If you have only 2 numbers in the range it will count
only those 2. If you have 6 it will count 6.
=SUM(LARGE(A27:Z27,ROW(INDIRECT("1:"&COUNT(A27:Z27 )))))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"okracerx" wrote in message
...
Thanks Don,
Works great, but what if one of my cells has text "DNF", value is zero.
but
when i use the above forumla it give me a #num error. Thanks

"Don Guillett" wrote:

=SUM(LARGE(A27:J27,{1,2,3,4,5,6}))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"okracerx" wrote in message
...
I need to be able to calculate the sum of, "the best of 6". Here is an
example:

Andy Harris 60 54 48 38 60 60 60 22 34

Total is 342

Would like a formula that would figure this out for me.

Thanks

.

But if there are 26 entries, it'll sum all of them.

Maybe:
=SUM(LARGE(A27:Z27,ROW(INDIRECT("1:"&MIN(6,COUNT(A 27:Z27))))))
(still array entered)
(sums no more than 6 entries)

And if there may not be any numbers:
=IF(COUNT(A27:Z27)=0,"No numbers",
SUM(LARGE(A27:Z27,ROW(INDIRECT("1:"&MIN(6,COUNT(A2 7:Z27)))))))
(still array entered)






Don Guillett wrote:

"DNF". Are you involved with racing? I was once an SCCA Formula Ford driver
and car importer.

That's because you have less than 6 number entries. You could increase the
range and put in some 0's
So use this instead. Be advised that you must enter using CSE
(control+shift+enter). If you have only 2 numbers in the range it will count
only those 2. If you have 6 it will count 6.
=SUM(LARGE(A27:Z27,ROW(INDIRECT("1:"&COUNT(A27:Z27 )))))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"okracerx" wrote in message
...
Thanks Don,
Works great, but what if one of my cells has text "DNF", value is zero.
but
when i use the above forumla it give me a #num error. Thanks

"Don Guillett" wrote:

=SUM(LARGE(A27:J27,{1,2,3,4,5,6}))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"okracerx" wrote in message
...
I need to be able to calculate the sum of, "the best of 6". Here is an
example:

Andy Harris 60 54 48 38 60 60 60 22 34

Total is 342

Would like a formula that would figure this out for me.

Thanks

.


--

Dave Peterson

Or restrict the range to 6 columns. OR
=SUM(LARGE(A27:Z27,ROW(INDIRECT("1:"&IF(COUNT(A27: Z27)6,6,COUNT(A27:Z27))))))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dave Peterson" wrote in message
...
But if there are 26 entries, it'll sum all of them.

Maybe:
=SUM(LARGE(A27:Z27,ROW(INDIRECT("1:"&MIN(6,COUNT(A 27:Z27))))))
(still array entered)
(sums no more than 6 entries)

And if there may not be any numbers:
=IF(COUNT(A27:Z27)=0,"No numbers",
SUM(LARGE(A27:Z27,ROW(INDIRECT("1:"&MIN(6,COUNT(A2 7:Z27)))))))
(still array entered)






Don Guillett wrote:

"DNF". Are you involved with racing? I was once an SCCA Formula Ford
driver
and car importer.

That's because you have less than 6 number entries. You could increase
the
range and put in some 0's
So use this instead. Be advised that you must enter using CSE
(control+shift+enter). If you have only 2 numbers in the range it will
count
only those 2. If you have 6 it will count 6.
=SUM(LARGE(A27:Z27,ROW(INDIRECT("1:"&COUNT(A27:Z27 )))))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"okracerx" wrote in message
...
Thanks Don,
Works great, but what if one of my cells has text "DNF", value is
zero.
but
when i use the above forumla it give me a #num error. Thanks

"Don Guillett" wrote:

=SUM(LARGE(A27:J27,{1,2,3,4,5,6}))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"okracerx" wrote in message
...
I need to be able to calculate the sum of, "the best of 6". Here is
an
example:

Andy Harris 60 54 48 38 60 60 60 22 34

Total is 342

Would like a formula that would figure this out for me.

Thanks

.


--

Dave Peterson

After looking at the file and getting more detail, I send OP this

Sub PlaceFormulasSAS() 'Calculates column N
Application.ScreenUpdating = False
Application.Calculation = xlManual
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
cn = 7
If IsNumeric(Cells(i, 2)) And Cells(i, 2) 0 Then
If Cells(i, 1).End(xlUp).Interior.ColorIndex = 10 Then cn = cn - 1
Cells(i, "N").FormulaArray = _
"=IF(COUNTa(C" & i & ":K" & i & ")<5,"""",SUM(LARGE(C" & i & ":K" & i &
",ROW(INDIRECT(""1:""&IF(COUNT(C" & i & ":K" & i & ")" & cn & "," & cn &
",COUNT(C" & i & ":K" & i & ")))))))"
End If
Next i

'comment line below to leave the formula
Columns("n").Value = Columns("n").Value

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"okracerx" wrote in message
...
I need to be able to calculate the sum of, "the best of 6". Here is an
example:

Andy Harris 60 54 48 38 60 60 60 22 34

Total is 342

Would like a formula that would figure this out for me.

Thanks