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 D10165536. So there is no difference in
efficiency between these formulas:

=SUM(D3100)
=SUM(D365536)

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(D365536) and not worry too much about it.

--
Rick (MVP - Excel)


"itsAchint" wrote in message
news
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) 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(D365536)
--
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 !!

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 D10165536. So there is no difference in
efficiency between these formulas:

=SUM(D3100)
=SUM(D365536)

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(D365536) and not worry too much about it.

--
Rick (MVP - Excel)


"itsAchint" wrote in message
news 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) 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(D365536)
--
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 !!

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 D10165536. So there is no difference in
efficiency between these formulas:

=SUM(D3100)
=SUM(D365536)

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(D365536) and not worry too much
about it.

--
Rick (MVP - Excel)


"itsAchint" wrote in message
news 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) 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(D365536)
--
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 !!

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 D10165536. So there is no difference in
efficiency between these formulas:

=SUM(D3100)
=SUM(D365536)

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(D365536) and not worry too much
about it.

--
Rick (MVP - Excel)


"itsAchint" wrote in message
news 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) 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(D365536)
--
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 !!

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 D10165536. So there is no difference in
efficiency between these formulas:

=SUM(D3100)
=SUM(D365536)

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(D365536) and not worry too much
about it.

--
Rick (MVP - Excel)


"itsAchint" wrote in message
news 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) 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(D365536)
--
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 !!

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 D10165536. So there is no difference
in
efficiency between these formulas:

=SUM(D3100)
=SUM(D365536)

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(D365536) and not worry too much
about it.

--
Rick (MVP - Excel)


"itsAchint" wrote in message
news 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) 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(D365536)
--
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 !!

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 D10165536. So there is no difference
in
efficiency between these formulas:

=SUM(D3100)
=SUM(D365536)

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(D365536) and not worry too much
about it.

--
Rick (MVP - Excel)


"itsAchint" wrote in message
news 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) 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(D365536)
--
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 !!

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 D10165536. So there is no
difference
in
efficiency between these formulas:

=SUM(D3100)
=SUM(D365536)

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(D365536) and not worry too much
about it.

--
Rick (MVP - Excel)


"itsAchint" wrote in message
news 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) 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(D365536)
--
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 !!