Sum Variable Ranges
 

I have a spreadsheet and I would like to add up a range of numbers but the
range is variable - I did a named range and that worked great until I did a
sort.

Is it possible for me to have a sum function stop when it reaches a cell
that has the word "stop" typed in a cell? Or can I type a number in a cell
and the number would represent the row number I would like the sum to stop at?

Any suggestions would be great!
Thanks,
Erika

Erika,

=SUM(INDIRECT("A1:A" & MATCH("Stop",A:A,FALSE)))

HTH,
Bernie
MS Excel MVP

"Erika" wrote in message
...
I have a spreadsheet and I would like to add up a range of numbers but the
range is variable - I did a named range and that worked great until I did
a
sort.

Is it possible for me to have a sum function stop when it reaches a cell
that has the word "stop" typed in a cell? Or can I type a number in a
cell
and the number would represent the row number I would like the sum to stop
at?

Any suggestions would be great!
Thanks,
Erika

Erika,

For your second idea,

=SUM(INDIRECT("A1:A" & B1))

Enter the stopping row number into cell B1.

HTH,
Bernie
MS Excel MVP

"Erika" wrote in message
...
I have a spreadsheet and I would like to add up a range of numbers but the
range is variable - I did a named range and that worked great until I did
a
sort.

Is it possible for me to have a sum function stop when it reaches a cell
that has the word "stop" typed in a cell? Or can I type a number in a
cell
and the number would represent the row number I would like the sum to stop
at?

Any suggestions would be great!
Thanks,
Erika

To sum col. A, starting at A1 and ending at row X, use:

=SUM(OFFSET(A1,,,F1))

where F1 holds X. Or with the word "stop" in col. B next
to the last row to sum:

=SUM(OFFSET(A1,,,MATCH("stop",B:B,0)))

HTH
Jason
Atlanta, GA


-----Original Message-----
I have a spreadsheet and I would like to add up a range
of numbers but the
range is variable - I did a named range and that worked
great until I did a
sort.

Is it possible for me to have a sum function stop when
it reaches a cell
that has the word "stop" typed in a cell? Or can I type
a number in a cell
and the number would represent the row number I would
like the sum to stop at?

Any suggestions would be great!
Thanks,
Erika
.

I used the following formula
=SUM(INDIRECT("D3:D"&MATCH("Stop",$D$3:$D$500,FALS E)))

The word stop is in cell D27 - it seems to be working great however it stops
summing at cell D25. Any ideas?

"Erika" wrote:

I have a spreadsheet and I would like to add up a range of numbers but the
range is variable - I did a named range and that worked great until I did a
sort.

Is it possible for me to have a sum function stop when it reaches a cell
that has the word "stop" typed in a cell? Or can I type a number in a cell
and the number would represent the row number I would like the sum to stop at?

Any suggestions would be great!
Thanks,
Erika

Hi
use:
=SUM(INDIRECT("D3:D"&MATCH("Stop",$D$3:$D$500,FALS E)+2))

--
Regards
Frank Kabel
Frankfurt, Germany

Erika wrote:
I used the following formula
=SUM(INDIRECT("D3:D"&MATCH("Stop",$D$3:$D$500,FALS E)))

The word stop is in cell D27 - it seems to be working great however
it stops summing at cell D25. Any ideas?

"Erika" wrote:

I have a spreadsheet and I would like to add up a range of numbers
but the range is variable - I did a named range and that worked
great until I did a sort.

Is it possible for me to have a sum function stop when it reaches a
cell
that has the word "stop" typed in a cell? Or can I type a number in
a cell and the number would represent the row number I would like
the sum to stop at?

Any suggestions would be great!
Thanks,
Erika

Erika,

Match returns the index of the cell that it matches, not necessarily the row
number. To get it to return the actual row number, either change the $D$3
to $D$1 so that the index and row numbers agree or, better yet, use all of
column D:

=SUM(INDIRECT("D3:D"&MATCH("Stop",D:D,FALSE)))

HTH,
Bernie
MS Excel MVP

"Erika" wrote in message
...
I used the following formula
=SUM(INDIRECT("D3:D"&MATCH("Stop",$D$3:$D$500,FALS E)))

The word stop is in cell D27 - it seems to be working great however it
stops
summing at cell D25. Any ideas?

"Erika" wrote:

I have a spreadsheet and I would like to add up a range of numbers but
the
range is variable - I did a named range and that worked great until I
did a
sort.

Is it possible for me to have a sum function stop when it reaches a cell
that has the word "stop" typed in a cell? Or can I type a number in a
cell
and the number would represent the row number I would like the sum to
stop at?

Any suggestions would be great!
Thanks,
Erika