Summing Question 1

[KevinFSI]

Afternoon all,
I need to make a cell equal to the sum of J3 to the end of column J.

I can't just say the sum of column J because I can't include row1 or row2 in the formula. I don't know how many rows will be in column J, but I need the sum from row3 to the end.

Any thoughts? Kevin
slanek@ssd.fsi.com

"Life is what happens to you while you're busy making other plans."
- John Lennon

 

[Zathras]

Not sure I understand the problem. You can simply use the formula

  =SUM(J3:J65536)

What am I missing? Is there something beyond the end of column J? If so, wouldn't that would be a contradiction in terms?

 

[KenWright]

If you aren't putting the formula into cells J1 or J2 then you could use the following:-

=SUM(INDIRECT("J3:J"&COUNTA(J:J))) as long as there were no blanks in the range you were summing

but if it is to go into one of those two cells, or you have blanks in the range you could simply use:-

=SUM(INDIRECT("J3:J"&COUNTBLANK(EZ:EZ))) with EZ simply being well away from your main body of data.

That eliminates the need to use a specific number of rows such as 65,536 which would bomb if it was opened up in an earlier version of Excel.

Regards
Ken...........

 

[KevinFSI]

Ken,
Exactly! I don't like to hard code numbers (like 65,536) into formulas. Never a good idea.

Your first formula will work fine. It was the ampersand I was missing. Thanks. Kevin
slanek@ssd.fsi.com

"Life is what happens to you while you're busy making other plans."
- John Lennon