Sum of a column from nth row to end

  • Thread starter Thread starter itsAchint
  • Start date Start date
I

itsAchint

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 !!
 
Since it doesn't matter if you include blank or text cells in the SUM
function, why not:

=SUM(D3:D65536)
 
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.
 
itsAchint said:
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 !!
Click to expand...


Try...

=SUM(D3:INDEX(D3:D65536,MATCH(9.99999999999999E+307,D3:D65536)))
 
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.
 
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!
=====================================================

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.
 
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
 
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 !
-----------------------------------------------------------------------------------------------

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!
=====================================================

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.
 
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.
 
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!
 
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.
 
So, are you saying that the first 2 formulas will evaluate differently then
the third?

=SUMPRODUCT((B1:B65535=D1)*(C1:C65535=D2)*A1:A65535)

=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.
 
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.
 
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
 
Yes.

(B1:B65535=D1)*(C1:C65535=D2)

That is an array even if you wrap inside INDEX:

INDEX((B1:B65535=D1)*(C1:C65535=D2),)
 
Back
Top