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?

=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)

"Frustrated" wrote:

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

=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

"Frustrated" wrote:

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?

this one is good...
;-)

I didn't have to array enter this one.

Mike H wrote:

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

"Frustrated" wrote:

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?


--

Dave Peterson

One mo

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

Frustrated wrote:

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?

--

Dave Peterson

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

"Dave Peterson" wrote:

I didn't have to array enter this one.

Mike H wrote:

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

"Frustrated" wrote:

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?


--

Dave Peterson

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.

"Joel" wrote:

=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)

"Frustrated" wrote:

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?

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.

"Mike H" wrote:

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

"Frustrated" wrote:

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?

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.

"Dave Peterson" wrote:

I didn't have to array enter this one.

Mike H wrote:

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

"Frustrated" wrote:

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?


--

Dave Peterson

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.

"Dave Peterson" wrote:

One mo

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

Frustrated wrote:

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?

--

Dave Peterson

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.

"Dave Peterson" wrote:

One mo

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

Frustrated wrote:

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?

--

Dave Peterson

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

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


"Frustrated" wrote:

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?

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



"Dave Peterson" wrote:

One mo

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

Frustrated wrote:

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?

--

Dave Peterson

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,2 0,......100}

So, you do this:

ROW(INDIRECT("1:100"))

--
Biff
Microsoft Excel MVP


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



"Dave Peterson" wrote:

One mo

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

Frustrated wrote:

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?

--

Dave Peterson

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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,2 0,......100}

So, you do this:

ROW(INDIRECT("1:100"))

--
Biff
Microsoft Excel MVP


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



"Dave Peterson" wrote:

One mo

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

Frustrated wrote:

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?

--

Dave Peterson

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.

--



"T. Valko" wrote:

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,2 0,......100}

So, you do this:

ROW(INDIRECT("1:100"))

--
Biff
Microsoft Excel MVP


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



"Dave Peterson" wrote:

One mo

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

Frustrated wrote:

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?

--

Dave Peterson

Check Biff's last response.

Brad wrote:

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.

--

"T. Valko" wrote:

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,2 0,......100}

So, you do this:

ROW(INDIRECT("1:100"))

--
Biff
Microsoft Excel MVP


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



"Dave Peterson" wrote:

One mo

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

Frustrated wrote:

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?

--

Dave Peterson





--

Dave Peterson

On Mon, 21 Jul 2008 04:16:01 -0700, Frustrated
wrote:

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

On Mon, 21 Jul 2008 17:04:22 GMT, Lars-Åke Aspelin
wrote:

On Mon, 21 Jul 2008 04:16:01 -0700, Frustrated
wrote:

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



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

Ahhhh! Thank you!!!


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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,2 0,......100}

So, you do this:

ROW(INDIRECT("1:100"))

--
Biff
Microsoft Excel MVP


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



"Dave Peterson" wrote:

One mo

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

Frustrated wrote:

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?

--

Dave Peterson

You're welcome!

--
Biff
Microsoft Excel MVP


"Brad" wrote in message
...
Ahhhh! Thank you!!!


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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,2 0,......100}

So, you do this:

ROW(INDIRECT("1:100"))

--
Biff
Microsoft Excel MVP


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



"Dave Peterson" wrote:

One mo

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

Frustrated wrote:

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?

--

Dave Peterson