variable cell into range
 

Saw this mentioned a couple weeks ago, but never saw an answer... now
I ran into it.
The spreadsheet is approximately 30 columns by 6000 rows, sorted by
column C which is date. I need to find the last row with an entry
(date) in column C, then sum everything in columns M,N from the top
(M5,N5) down to that last row. How can I do SUM(M5:cell in last used
row) and place that sum in A2, I know some VB code but don't know how
to put a variable in a formula. Is it possible to have code in cell A2
that automatically refigures after new entries are put in and sorted?
I am trying to show the remaining balance in those columns after all
payments to date are deducted.

variable cell into range
 

Hi Dan,

Have you tried the worksheet formula =SUM(M:N)? Or perhaps (SUM(M5:N65536)?

If there are some particular constraints due to the design of your
spreadsheet you may need to explain what they are.

Regards,

Peter Beach

"Dan" wrote in message
...
Saw this mentioned a couple weeks ago, but never saw an answer... now
I ran into it.
The spreadsheet is approximately 30 columns by 6000 rows, sorted by
column C which is date. I need to find the last row with an entry
(date) in column C, then sum everything in columns M,N from the top
(M5,N5) down to that last row. How can I do SUM(M5:cell in last used
row) and place that sum in A2, I know some VB code but don't know how
to put a variable in a formula. Is it possible to have code in cell A2
that automatically refigures after new entries are put in and sorted?
I am trying to show the remaining balance in those columns after all
payments to date are deducted.

variable cell into range
 

Dan,

I don't think you need to use any code to do this. Two
different ways spring to mind of which the first is
probably the best:

=SumIf(C2:C65536,"0",M2:M65536)
This will sum every cell in column M where there is a
positive entry in column C.

=SUM(M2:OFFSET(M2,0,0,COUNTA(C2:C65536),1))

This will count the number of entries in column C and then
sum that number of entries down from cell M2. Problems
would arise here if you have some rows with blank entries
which would be avoided using the SUMIF method.

I was uncertain whether you wanted separate totals for
your two columns, in which case just modify the initial
formula in the next cell, or one total, in which case this
should work:

=SumIf(C2:C65536,"0",M2:M65536)+SumIF
(C2:C65536,"0",N2:N65536)

Cheers, Pete

variable cell into range
 

I believe the SumIf may do it, the sheet is completely filled with
numeric values, I only want them summed if they have been paid to
date. This is then deducted from the column total. The 2 columns
respectively are a total and a commision, and would be separate
totals.

Thank you!


On Tue, 26 Aug 2003 04:53:50 -0700, "Pete McCosh"
wrote:

Dan,

I don't think you need to use any code to do this. Two
different ways spring to mind of which the first is
probably the best:

=SumIf(C2:C65536,"0",M2:M65536)
This will sum every cell in column M where there is a
positive entry in column C.

=SUM(M2:OFFSET(M2,0,0,COUNTA(C2:C65536),1))

This will count the number of entries in column C and then
sum that number of entries down from cell M2. Problems
would arise here if you have some rows with blank entries
which would be avoided using the SUMIF method.

I was uncertain whether you wanted separate totals for
your two columns, in which case just modify the initial
formula in the next cell, or one total, in which case this
should work:

=SumIf(C2:C65536,"0",M2:M65536)+SumIF
(C2:C65536,"0",N2:N65536)

Cheers, Pete