End of column
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 |
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" wrote in message news:Brisbane.Rob.1slaad_1122066319.2986@excelforu m-nospam.com... 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 |
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" wrote in message ... 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" wrote in message news:Brisbane.Rob.1slaad_1122066319.2986@excelforu m-nospam.com... 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 |
All times are GMT +1. The time now is 02:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com