Sum Variable Ranges

[Guest]

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

 

[Bernie Deitrick]

Erika,

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

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Erika,

For your second idea,

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

Enter the stopping row number into cell B1.

HTH,
Bernie
MS Excel MVP

 

[Jason Morin]

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

 

[Guest]

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

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

 

[Frank Kabel]

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

 

[Bernie Deitrick]

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"&MATCH("Stop",D,FALSE)))

HTH,
Bernie
MS Excel MVP