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