Adding top 10 numbers from 100

[Frustrated]

How do I add the top 10 numbers out of 100 numbers that are not in a row or
column but scattered throughout a spreadsheet. For instance, the first number
might be in cell a2, second in cell ba15, and so on.

How would I write the formula to sum the top something numbers?

 

[Joel]

=LARGE(A1:B100,1)+LARGE(A1:B100,2)+LARGE(A1:B100,3)+LARGE(A1:B100,4)+LARGE(A1:B100,5)+LARGE(A1:B100,6)+LARGE(A1:B100,7)+LARGE(A1:B100,8)+LARGE(A1:B100,9)+LARGE(A1:B100,10)

 

[Mike H]

Try

=SUM(LARGE(A1:H100, {1,2,3,4,5,6,7,8,9,10}))

Which is an Array so enter with Ctrl+Shuft+Enter

Mike

 

[Jarek Kujawa]

this one is good...
;-)

 

[Dave Peterson]

I didn't have to array enter this one.

Try

=SUM(LARGE(A1:H100, {1,2,3,4,5,6,7,8,9,10}))

Which is an Array so enter with Ctrl+Shuft+Enter

Mike

 

[Dave Peterson]

One more:

=SUMPRODUCT(LARGE(A1:H100,ROW(INDIRECT("1:10"))))

 

[Mike H]

Dave,

Your absolutely correct, because I had an array of numbers it gave me an
(incorrect) mindset the formula was an array.

Thanks for the correction.

Mike

 

[Frustrated]

Hey, I forgot to mention that the spreadsheet has a total of 500 numbers of
which the know numbers relevant for the top 10 sum result are 100. There are
thus many other numbers some small some large in same spreadsheet which
should be excluded from the formula. The numbers are in no row order or
column order. I know the cell numbers where the relevant numbers are located
though.

 

[Frustrated]

Hey, I forgot to mention that the spreadsheet has a total of 500 numbers of
which the know numbers relevant for the top 10 sum result are 100. There are
thus many other numbers some small some large in same spreadsheet which
should be excluded from the formula. The numbers are in no row order or
column order. I know the cell numbers where the relevant numbers are located
though.

 

[Frustrated]

Hey, I forgot to mention that the spreadsheet has a total of 500 numbers of
which the know numbers relevant for the top 10 sum result are 100. There are
thus many other numbers some small some large in same spreadsheet which
should be excluded from the formula. The numbers are in no row order or
column order. I know the cell numbers where the relevant numbers are located
though.

 

[Frustrated]

Hey, I forgot to mention that the spreadsheet has a total of 500 numbers of
which the know numbers relevant for the top 10 sum result are 100. There are
thus many other numbers some small some large in same spreadsheet which
should be excluded from the formula. The numbers are in no row order or
column order. I know the cell numbers where the relevant numbers are located
though.

 

[Frustrated]

Hey, I forgot to mention that the spreadsheet has a total of 500 numbers of
which the know numbers relevant for the top 10 sum result are 100. There are
thus many other numbers some small some large in same spreadsheet which
should be excluded from the formula. The numbers are in no row order or
column order. I know the cell numbers where the relevant numbers are located
though.

 

[John C]

=SUMIF(List,">"&LARGE(List,LgList),List)+(LgList-COUNTIF(List,">"&LARGE(List,LgList)))*LARGE(List,LgList)

where List is the range of cells that you are evauluating
and LgList is the top X number that you want to sum.

 

[Brad]

Don't see why the Indirect is needed - seems to work without it.... But
please enlighten me...

 

[T. Valko]

What if they wanted to sum the largest 100 numbers out of 1000?

You wouldn't want to do this:

{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,......100}

So, you do this:

ROW(INDIRECT("1:100"))

 

[T. Valko]

I think I may have misunderstood your question!

You use INDIRECT to make it robust against row insertions.

This will work:

ROW(1:10)

However, if you insert new rows above or within the range the formula will
break. For example, if you inserted a new row 1 the ROW(1:10) becomes
ROW(2:11). Using INDIRECT accounts for row insertions. ROW(INDIRECT("1:10"))
will *always* refer to 1:10.

 

[Brad]

When I keyed in
=SUMPRODUCT(LARGE(A1:H100,ROW(1:10)))
it worked and so does
=SUMPRODUCT(LARGE(A1:H100,ROW(1:100)))

So, I'm curious why the indirect is needed.

 

[Dave Peterson]

Check Biff's last response.

When I keyed in
=SUMPRODUCT(LARGE(A1:H100,ROW(1:10)))
it worked and so does
=SUMPRODUCT(LARGE(A1:H100,ROW(1:100)))

So, I'm curious why the indirect is needed.

--

 

[Lars-Åke Aspelin]

How do I add the top 10 numbers out of 100 numbers that are not in a row or
column but scattered throughout a spreadsheet. For instance, the first number
might be in cell a2, second in cell ba15, and so on.

How would I write the formula to sum the top something numbers?

Try this formula
Note that it is an array formula and has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

=SUM(LARGE((myrange)*(mymap),ROW(1:10)))

myrange is a named range big enough to cover all interesting data
mymap is a named range with the same size but perhaps on another
sheet.

In mymap you mark with 1 each of the 100 (scattered) cells that
corresponds to the 100 numbers that you are interested in. the other
cells should remain blank.

The formula will result in the sum of the top 10 of these numbers.

WARNING: if the layout of the sheet with myrange, you have to update
mymap as well.

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

Try this formula
Note that it is an array formula and has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

=SUM(LARGE((myrange)*(mymap),ROW(1:10)))

myrange is a named range big enough to cover all interesting data
mymap is a named range with the same size but perhaps on another
sheet.

In mymap you mark with 1 each of the 100 (scattered) cells that
corresponds to the 100 numbers that you are interested in. the other
cells should remain blank.

The formula will result in the sum of the top 10 of these numbers.

WARNING: if the layout of the sheet with myrange, you have to update
mymap as well.

Hope this helps / Lars-Åke

if you change the layout.... I meant, but missed out two words.