Add top numbers
 

How do you add the top 20 numbers from a list of 500 numbers in a row?

Add top numbers
 

=SUMPRODUCT(--(RANK(list_of_values,list_of_values,0)<=20),list_o f_values)
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Frustrated" wrote:

How do you add the top 20 numbers from a list of 500 numbers in a row?

Add top numbers
 

This formula will break if the 20th largest value has duplicates.
Try instead:
=SUMIF(List,""&LARGE(List,LgList),List)+(LgList-COUNTIF(List,""&LARGE(List,LgList)))*LARGE(List,L gList)

where List is the range of numbers, and LgList is, in this case, 20.


--
John C


"M Kan" wrote:

=SUMPRODUCT(--(RANK(list_of_values,list_of_values,0)<=20),list_o f_values)
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Frustrated" wrote:

How do you add the top 20 numbers from a list of 500 numbers in a row?

Add top numbers
 

Try this:

=SUM(INDEX(LARGE(A1:A500,ROW(1:20)),0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Frustrated" wrote in message
...
How do you add the top 20 numbers from a list of 500 numbers in a row?

Add top numbers
 

nice!
--
John C


"RagDyer" wrote:

Try this:

=SUM(INDEX(LARGE(A1:A500,ROW(1:20)),0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Frustrated" wrote in message
...
How do you add the top 20 numbers from a list of 500 numbers in a row?