Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return actual range not cell value in variable | Excel Worksheet Functions | |||
Storing a range variable in a cell | Excel Discussion (Misc queries) | |||
Variable Cell Range Reference | Excel Worksheet Functions | |||
VLOOKUP variable range cell reference | Excel Worksheet Functions | |||
how to place a variable cell into a range? | Excel Programming |