| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of a column from nth row to end
I want to total a column from D3 to the end of D column (I don't know the
end) and place that sum in D1. Please help !! Urgent !! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
Since it doesn't matter if you include blank or text cells in the SUM
function, why not: =SUM(D3:D65536) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "itsAchint" wrote: I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
Luke,
I know I could do that but I was thinking whether we have some other options if we don't want to sum that long. I know we can sum a whole column using SUM(D:D) but you cannot place that SUM in the same column. "Luke M" wrote: Since it doesn't matter if you include blank or text cells in the SUM function, why not: =SUM(D3:D65536) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "itsAchint" wrote: I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
In article ,
itsAchint wrote: I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! Try... =SUM(D3:INDEX(D3:D65536,MATCH(9.99999999999999E+30 7,D3:D65536))) -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
Alternative ideas:
=SUM(OFFSET(D3,0,0,COUNT(D3:D65536),1)) Offset creates a range with height based on number of numbers in column D. =SUM(INDIRECT("D3:D"&COUNT(D3:D65536)+3)) Indirect lets you manually create a reference, control row number via COUNT in this example (but you could use a cell reference) The SUM function is rather efficient however, so I would recommend just going with that. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "itsAchint" wrote: Luke, I know I could do that but I was thinking whether we have some other options if we don't want to sum that long. I know we can sum a whole column using SUM(D:D) but you cannot place that SUM in the same column. "Luke M" wrote: Since it doesn't matter if you include blank or text cells in the SUM function, why not: =SUM(D3:D65536) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "itsAchint" wrote: I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
Try this:
=SUM(D3:INDEX(D:D,MATCH(99^99,D:D))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "itsAchint" wrote in message ... Luke, I know I could do that but I was thinking whether we have some other options if we don't want to sum that long. I know we can sum a whole column using SUM(D:D) but you cannot place that SUM in the same column. "Luke M" wrote: Since it doesn't matter if you include blank or text cells in the SUM function, why not: =SUM(D3:D65536) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "itsAchint" wrote: I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
Hi,
Select the numbers in column D (say in range D3:D12) and press Ctrl+L (to convert the range to a list). Keep the box for "My data has header rows" unchecked if D3 is not the header row. In cell D1, enter the formula =sum(D3:D12). Now when you add data in D13, the sum will auto update. This will only work with Excel 2003 and above. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "itsAchint" wrote in message ... I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
Didn't notice you wanted to place the total in Column D.
Try this: =SUM(D3:INDEX(D3:D10000,MATCH(99^99,D3:D10000))) -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "RagDyeR" wrote in message ... Try this: =SUM(D3:INDEX(D:D,MATCH(99^99,D:D))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "itsAchint" wrote in message ... Luke, I know I could do that but I was thinking whether we have some other options if we don't want to sum that long. I know we can sum a whole column using SUM(D:D) but you cannot place that SUM in the same column. "Luke M" wrote: Since it doesn't matter if you include blank or text cells in the SUM function, why not: =SUM(D3:D65536) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "itsAchint" wrote: I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
I don't know this for sure, but my "gut" tells me that Excel will stop at
the limits of what in the VBA world is know as the UsedRange. The UsedRange is, theoretically, the maximum range of cells in use (I said theoretically, because deleting cell content via VB code can some times leave the UsedRange larger than it should be). Excel tracks the extent of the cells in use and it seems unlikely it would permit a calculation to extend into what it "knows" is a range of cells that can have no data. So, I would simply use =SUM(D3:D65536) and not worry too much about it. -- Rick (MVP - Excel) "itsAchint" wrote in message ... Luke, I know I could do that but I was thinking whether we have some other options if we don't want to sum that long. I know we can sum a whole column using SUM(D:D) but you cannot place that SUM in the same column. "Luke M" wrote: Since it doesn't matter if you include blank or text cells in the SUM function, why not: =SUM(D3:D65536) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "itsAchint" wrote: I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
Exactly!
In other words, if the last row that contains an entry is D100, Excel doesn't bother checking cells D101:D65536. So there is no difference in efficiency between these formulas: =SUM(D3:D100) =SUM(D3:D65536) This does not apply to all functions/formulas, though! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I don't know this for sure, but my "gut" tells me that Excel will stop at the limits of what in the VBA world is know as the UsedRange. The UsedRange is, theoretically, the maximum range of cells in use (I said theoretically, because deleting cell content via VB code can some times leave the UsedRange larger than it should be). Excel tracks the extent of the cells in use and it seems unlikely it would permit a calculation to extend into what it "knows" is a range of cells that can have no data. So, I would simply use =SUM(D3:D65536) and not worry too much about it. -- Rick (MVP - Excel) "itsAchint" wrote in message ... Luke, I know I could do that but I was thinking whether we have some other options if we don't want to sum that long. I know we can sum a whole column using SUM(D:D) but you cannot place that SUM in the same column. "Luke M" wrote: Since it doesn't matter if you include blank or text cells in the SUM function, why not: =SUM(D3:D65536) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "itsAchint" wrote: I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
What about Sumproduct Biff?
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... Exactly! In other words, if the last row that contains an entry is D100, Excel doesn't bother checking cells D101:D65536. So there is no difference in efficiency between these formulas: =SUM(D3:D100) =SUM(D3:D65536) This does not apply to all functions/formulas, though! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I don't know this for sure, but my "gut" tells me that Excel will stop at the limits of what in the VBA world is know as the UsedRange. The UsedRange is, theoretically, the maximum range of cells in use (I said theoretically, because deleting cell content via VB code can some times leave the UsedRange larger than it should be). Excel tracks the extent of the cells in use and it seems unlikely it would permit a calculation to extend into what it "knows" is a range of cells that can have no data. So, I would simply use =SUM(D3:D65536) and not worry too much about it. -- Rick (MVP - Excel) "itsAchint" wrote in message ... Luke, I know I could do that but I was thinking whether we have some other options if we don't want to sum that long. I know we can sum a whole column using SUM(D:D) but you cannot place that SUM in the same column. "Luke M" wrote: Since it doesn't matter if you include blank or text cells in the SUM function, why not: =SUM(D3:D65536) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "itsAchint" wrote: I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
SUMPRODUCT and any other array processing formulas will evaluate *every*
cell referenced by the formula. In Excel 2007, something like: =SUMPRODUCT(--(A:A="x"),--(B:B="z")) is "crazy" if you don't actually have data in every row. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... What about Sumproduct Biff? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... Exactly! In other words, if the last row that contains an entry is D100, Excel doesn't bother checking cells D101:D65536. So there is no difference in efficiency between these formulas: =SUM(D3:D100) =SUM(D3:D65536) This does not apply to all functions/formulas, though! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I don't know this for sure, but my "gut" tells me that Excel will stop at the limits of what in the VBA world is know as the UsedRange. The UsedRange is, theoretically, the maximum range of cells in use (I said theoretically, because deleting cell content via VB code can some times leave the UsedRange larger than it should be). Excel tracks the extent of the cells in use and it seems unlikely it would permit a calculation to extend into what it "knows" is a range of cells that can have no data. So, I would simply use =SUM(D3:D65536) and not worry too much about it. -- Rick (MVP - Excel) "itsAchint" wrote in message ... Luke, I know I could do that but I was thinking whether we have some other options if we don't want to sum that long. I know we can sum a whole column using SUM(D:D) but you cannot place that SUM in the same column. "Luke M" wrote: Since it doesn't matter if you include blank or text cells in the SUM function, why not: =SUM(D3:D65536) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "itsAchint" wrote: I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
So, are you saying that the first 2 formulas will evaluate differently then
the third? =SUMPRODUCT((B1:B65535=D1)*(C1:C65535=D2)*A1:A6553 5) =INDEX(A1:A65535,MATCH(1,(B1:B65535=D1)*(C1:C65535 =D2),0)) =INDEX(A1:A65535,MATCH(1,INDEX((B1:B65535=D1)*(C1: C65535=D2),),0)) I made sure no matches existed, so they would all need to go "full range". I don't have XL07 to try them on a larger range, but I can't *see* any difference between the 3 in XL2K. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... SUMPRODUCT and any other array processing formulas will evaluate *every* cell referenced by the formula. In Excel 2007, something like: =SUMPRODUCT(--(A:A="x"),--(B:B="z")) is "crazy" if you don't actually have data in every row. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... What about Sumproduct Biff? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... Exactly! In other words, if the last row that contains an entry is D100, Excel doesn't bother checking cells D101:D65536. So there is no difference in efficiency between these formulas: =SUM(D3:D100) =SUM(D3:D65536) This does not apply to all functions/formulas, though! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I don't know this for sure, but my "gut" tells me that Excel will stop at the limits of what in the VBA world is know as the UsedRange. The UsedRange is, theoretically, the maximum range of cells in use (I said theoretically, because deleting cell content via VB code can some times leave the UsedRange larger than it should be). Excel tracks the extent of the cells in use and it seems unlikely it would permit a calculation to extend into what it "knows" is a range of cells that can have no data. So, I would simply use =SUM(D3:D65536) and not worry too much about it. -- Rick (MVP - Excel) "itsAchint" wrote in message ... Luke, I know I could do that but I was thinking whether we have some other options if we don't want to sum that long. I know we can sum a whole column using SUM(D:D) but you cannot place that SUM in the same column. "Luke M" wrote: Since it doesn't matter if you include blank or text cells in the SUM function, why not: =SUM(D3:D65536) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "itsAchint" wrote: I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
No, all 3 of those examples are processing arrays so *every* cell referenced
is being evaluated even though the used range may end at D100. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... So, are you saying that the first 2 formulas will evaluate differently then the third? =SUMPRODUCT((B1:B65535=D1)*(C1:C65535=D2)*A1:A6553 5) =INDEX(A1:A65535,MATCH(1,(B1:B65535=D1)*(C1:C65535 =D2),0)) =INDEX(A1:A65535,MATCH(1,INDEX((B1:B65535=D1)*(C1: C65535=D2),),0)) I made sure no matches existed, so they would all need to go "full range". I don't have XL07 to try them on a larger range, but I can't *see* any difference between the 3 in XL2K. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... SUMPRODUCT and any other array processing formulas will evaluate *every* cell referenced by the formula. In Excel 2007, something like: =SUMPRODUCT(--(A:A="x"),--(B:B="z")) is "crazy" if you don't actually have data in every row. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... What about Sumproduct Biff? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... Exactly! In other words, if the last row that contains an entry is D100, Excel doesn't bother checking cells D101:D65536. So there is no difference in efficiency between these formulas: =SUM(D3:D100) =SUM(D3:D65536) This does not apply to all functions/formulas, though! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I don't know this for sure, but my "gut" tells me that Excel will stop at the limits of what in the VBA world is know as the UsedRange. The UsedRange is, theoretically, the maximum range of cells in use (I said theoretically, because deleting cell content via VB code can some times leave the UsedRange larger than it should be). Excel tracks the extent of the cells in use and it seems unlikely it would permit a calculation to extend into what it "knows" is a range of cells that can have no data. So, I would simply use =SUM(D3:D65536) and not worry too much about it. -- Rick (MVP - Excel) "itsAchint" wrote in message ... Luke, I know I could do that but I was thinking whether we have some other options if we don't want to sum that long. I know we can sum a whole column using SUM(D:D) but you cannot place that SUM in the same column. "Luke M" wrote: Since it doesn't matter if you include blank or text cells in the SUM function, why not: =SUM(D3:D65536) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "itsAchint" wrote: I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
So what you're saying, is that even though the last formula is *not* array
entered, it's calculating in exactly the same manner as Sumproduct calculates. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... No, all 3 of those examples are processing arrays so *every* cell referenced is being evaluated even though the used range may end at D100. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... So, are you saying that the first 2 formulas will evaluate differently then the third? =SUMPRODUCT((B1:B65535=D1)*(C1:C65535=D2)*A1:A6553 5) =INDEX(A1:A65535,MATCH(1,(B1:B65535=D1)*(C1:C65535 =D2),0)) =INDEX(A1:A65535,MATCH(1,INDEX((B1:B65535=D1)*(C1: C65535=D2),),0)) I made sure no matches existed, so they would all need to go "full range". I don't have XL07 to try them on a larger range, but I can't *see* any difference between the 3 in XL2K. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... SUMPRODUCT and any other array processing formulas will evaluate *every* cell referenced by the formula. In Excel 2007, something like: =SUMPRODUCT(--(A:A="x"),--(B:B="z")) is "crazy" if you don't actually have data in every row. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... What about Sumproduct Biff? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... Exactly! In other words, if the last row that contains an entry is D100, Excel doesn't bother checking cells D101:D65536. So there is no difference in efficiency between these formulas: =SUM(D3:D100) =SUM(D3:D65536) This does not apply to all functions/formulas, though! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I don't know this for sure, but my "gut" tells me that Excel will stop at the limits of what in the VBA world is know as the UsedRange. The UsedRange is, theoretically, the maximum range of cells in use (I said theoretically, because deleting cell content via VB code can some times leave the UsedRange larger than it should be). Excel tracks the extent of the cells in use and it seems unlikely it would permit a calculation to extend into what it "knows" is a range of cells that can have no data. So, I would simply use =SUM(D3:D65536) and not worry too much about it. -- Rick (MVP - Excel) "itsAchint" wrote in message ... Luke, I know I could do that but I was thinking whether we have some other options if we don't want to sum that long. I know we can sum a whole column using SUM(D:D) but you cannot place that SUM in the same column. "Luke M" wrote: Since it doesn't matter if you include blank or text cells in the SUM function, why not: =SUM(D3:D65536) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "itsAchint" wrote: I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
Yes.
(B1:B65535=D1)*(C1:C65535=D2) That is an array even if you wrap inside INDEX: INDEX((B1:B65535=D1)*(C1:C65535=D2),) -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... So what you're saying, is that even though the last formula is *not* array entered, it's calculating in exactly the same manner as Sumproduct calculates. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... No, all 3 of those examples are processing arrays so *every* cell referenced is being evaluated even though the used range may end at D100. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... So, are you saying that the first 2 formulas will evaluate differently then the third? =SUMPRODUCT((B1:B65535=D1)*(C1:C65535=D2)*A1:A6553 5) =INDEX(A1:A65535,MATCH(1,(B1:B65535=D1)*(C1:C65535 =D2),0)) =INDEX(A1:A65535,MATCH(1,INDEX((B1:B65535=D1)*(C1: C65535=D2),),0)) I made sure no matches existed, so they would all need to go "full range". I don't have XL07 to try them on a larger range, but I can't *see* any difference between the 3 in XL2K. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... SUMPRODUCT and any other array processing formulas will evaluate *every* cell referenced by the formula. In Excel 2007, something like: =SUMPRODUCT(--(A:A="x"),--(B:B="z")) is "crazy" if you don't actually have data in every row. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... What about Sumproduct Biff? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... Exactly! In other words, if the last row that contains an entry is D100, Excel doesn't bother checking cells D101:D65536. So there is no difference in efficiency between these formulas: =SUM(D3:D100) =SUM(D3:D65536) This does not apply to all functions/formulas, though! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I don't know this for sure, but my "gut" tells me that Excel will stop at the limits of what in the VBA world is know as the UsedRange. The UsedRange is, theoretically, the maximum range of cells in use (I said theoretically, because deleting cell content via VB code can some times leave the UsedRange larger than it should be). Excel tracks the extent of the cells in use and it seems unlikely it would permit a calculation to extend into what it "knows" is a range of cells that can have no data. So, I would simply use =SUM(D3:D65536) and not worry too much about it. -- Rick (MVP - Excel) "itsAchint" wrote in message ... Luke, I know I could do that but I was thinking whether we have some other options if we don't want to sum that long. I know we can sum a whole column using SUM(D:D) but you cannot place that SUM in the same column. "Luke M" wrote: Since it doesn't matter if you include blank or text cells in the SUM function, why not: =SUM(D3:D65536) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "itsAchint" wrote: I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
Appreciate the input ... Thanks.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... Yes. (B1:B65535=D1)*(C1:C65535=D2) That is an array even if you wrap inside INDEX: INDEX((B1:B65535=D1)*(C1:C65535=D2),) -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... So what you're saying, is that even though the last formula is *not* array entered, it's calculating in exactly the same manner as Sumproduct calculates. -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "T. Valko" wrote in message ... No, all 3 of those examples are processing arrays so *every* cell referenced is being evaluated even though the used range may end at D100. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... So, are you saying that the first 2 formulas will evaluate differently then the third? =SUMPRODUCT((B1:B65535=D1)*(C1:C65535=D2)*A1:A6553 5) =INDEX(A1:A65535,MATCH(1,(B1:B65535=D1)*(C1:C65535 =D2),0)) =INDEX(A1:A65535,MATCH(1,INDEX((B1:B65535=D1)*(C1: C65535=D2),),0)) I made sure no matches existed, so they would all need to go "full range". I don't have XL07 to try them on a larger range, but I can't *see* any difference between the 3 in XL2K. -- Regards, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "T. Valko" wrote in message ... SUMPRODUCT and any other array processing formulas will evaluate *every* cell referenced by the formula. In Excel 2007, something like: =SUMPRODUCT(--(A:A="x"),--(B:B="z")) is "crazy" if you don't actually have data in every row. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... What about Sumproduct Biff? -- Regards, RD ----------------------------------------------------------------------- ---- Please keep all correspondence within the NewsGroup, so all may benefit ! ----------------------------------------------------------------------- ---- "T. Valko" wrote in message ... Exactly! In other words, if the last row that contains an entry is D100, Excel doesn't bother checking cells D101:D65536. So there is no difference in efficiency between these formulas: =SUM(D3:D100) =SUM(D3:D65536) This does not apply to all functions/formulas, though! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I don't know this for sure, but my "gut" tells me that Excel will stop at the limits of what in the VBA world is know as the UsedRange. The UsedRange is, theoretically, the maximum range of cells in use (I said theoretically, because deleting cell content via VB code can some times leave the UsedRange larger than it should be). Excel tracks the extent of the cells in use and it seems unlikely it would permit a calculation to extend into what it "knows" is a range of cells that can have no data. So, I would simply use =SUM(D3:D65536) and not worry too much about it. -- Rick (MVP - Excel) "itsAchint" wrote in message ... Luke, I know I could do that but I was thinking whether we have some other options if we don't want to sum that long. I know we can sum a whole column using SUM(D:D) but you cannot place that SUM in the same column. "Luke M" wrote: Since it doesn't matter if you include blank or text cells in the SUM function, why not: =SUM(D3:D65536) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "itsAchint" wrote: I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a column from nth row to end
You're welcome!
-- Biff Microsoft Excel MVP "Ragdyer" wrote in message ... Appreciate the input ... Thanks. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... Yes. (B1:B65535=D1)*(C1:C65535=D2) That is an array even if you wrap inside INDEX: INDEX((B1:B65535=D1)*(C1:C65535=D2),) -- Biff Microsoft Excel MVP "RagDyeR" wrote in message ... So what you're saying, is that even though the last formula is *not* array entered, it's calculating in exactly the same manner as Sumproduct calculates. -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "T. Valko" wrote in message ... No, all 3 of those examples are processing arrays so *every* cell referenced is being evaluated even though the used range may end at D100. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... So, are you saying that the first 2 formulas will evaluate differently then the third? =SUMPRODUCT((B1:B65535=D1)*(C1:C65535=D2)*A1:A6553 5) =INDEX(A1:A65535,MATCH(1,(B1:B65535=D1)*(C1:C65535 =D2),0)) =INDEX(A1:A65535,MATCH(1,INDEX((B1:B65535=D1)*(C1: C65535=D2),),0)) I made sure no matches existed, so they would all need to go "full range". I don't have XL07 to try them on a larger range, but I can't *see* any difference between the 3 in XL2K. -- Regards, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "T. Valko" wrote in message ... SUMPRODUCT and any other array processing formulas will evaluate *every* cell referenced by the formula. In Excel 2007, something like: =SUMPRODUCT(--(A:A="x"),--(B:B="z")) is "crazy" if you don't actually have data in every row. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... What about Sumproduct Biff? -- Regards, RD ----------------------------------------------------------------------- ---- Please keep all correspondence within the NewsGroup, so all may benefit ! ----------------------------------------------------------------------- ---- "T. Valko" wrote in message ... Exactly! In other words, if the last row that contains an entry is D100, Excel doesn't bother checking cells D101:D65536. So there is no difference in efficiency between these formulas: =SUM(D3:D100) =SUM(D3:D65536) This does not apply to all functions/formulas, though! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I don't know this for sure, but my "gut" tells me that Excel will stop at the limits of what in the VBA world is know as the UsedRange. The UsedRange is, theoretically, the maximum range of cells in use (I said theoretically, because deleting cell content via VB code can some times leave the UsedRange larger than it should be). Excel tracks the extent of the cells in use and it seems unlikely it would permit a calculation to extend into what it "knows" is a range of cells that can have no data. So, I would simply use =SUM(D3:D65536) and not worry too much about it. -- Rick (MVP - Excel) "itsAchint" wrote in message ... Luke, I know I could do that but I was thinking whether we have some other options if we don't want to sum that long. I know we can sum a whole column using SUM(D:D) but you cannot place that SUM in the same column. "Luke M" wrote: Since it doesn't matter if you include blank or text cells in the SUM function, why not: =SUM(D3:D65536) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "itsAchint" wrote: I want to total a column from D3 to the end of D column (I don't know the end) and place that sum in D1. Please help !! Urgent !! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| divide column(x) by column(y) to give column(x/y) in excel? | New Users to Excel | |||
| Referencing date column A & time column B to get info from column | Excel Discussion (Misc queries) | |||
| Return text in Column A if Column B and Column K match | Excel Worksheet Functions | |||
| Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
| what formula do i put for column m = column k minus column l in e. | Excel Discussion (Misc queries) | |||
Linear Mode
