End of column

[Brisbane Rob]

We cut and paste data into a spreadsheet and the data includes a variable number of rows holding numeric data and then text. How do I put in a sum formula which will sum from a known starting cell down to a cell (varying) without trying to add in the text?.

[RagDyer]

The Sum() function ignores text.

You could very well use a formula like:

=SUM(A:A)
OR
=SUM(A10:A65000)

However, if you really just want to reference your last numerical cell, with
your starting cell at A10, you can try something like this:

=SUM(A10:INDIRECT(ADDRESS(MATCH(9.99999999999999E+307,A:A),1)))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Brisbane Rob" <[email protected]>
wrote in message
news:[email protected]...
>
> We cut and paste data into a spreadsheet and the data includes a
> variable number of rows holding numeric data and then text. How do I
> put in a sum formula which will sum from a known starting cell down to
> a cell (varying) without trying to add in the text?.
>
>
> --
> Brisbane Rob
> ------------------------------------------------------------------------
> Brisbane Rob's Profile:
http://www.excelforum.com/member.php...o&userid=25096
> View this thread: http://www.excelforum.com/showthread...hreadid=389482
>

[KL]

or you can also try these:

=SUM(A10:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

or

=SUM(OFFSET(A10,,,MATCH(9.99999999999999E+307,A:A)-9))

Regards,
KL


"RagDyer" <[email protected]> wrote in message
news:[email protected]...
> The Sum() function ignores text.
>
> You could very well use a formula like:
>
> =SUM(A:A)
> OR
> =SUM(A10:A65000)
>
> However, if you really just want to reference your last numerical cell,
> with
> your starting cell at A10, you can try something like this:
>
> =SUM(A10:INDIRECT(ADDRESS(MATCH(9.99999999999999E+307,A:A),1)))
>
> --
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
> "Brisbane Rob" <[email protected]>
> wrote in message
> news:[email protected]...
>>
>> We cut and paste data into a spreadsheet and the data includes a
>> variable number of rows holding numeric data and then text. How do I
>> put in a sum formula which will sum from a known starting cell down to
>> a cell (varying) without trying to add in the text?.
>>
>>
>> --
>> Brisbane Rob
>> ------------------------------------------------------------------------
>> Brisbane Rob's Profile:
> http://www.excelforum.com/member.php...o&userid=25096
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=389482
>>
>
>