Column Total Shows Dashes...

I have a column to total. At the bottom of the column is a cell where the total for that column is calculated. The calculation portion of the function I am using seems to work fine. However, this cell displays a dash until there is some data entered into one of the cells in the column. I am currently formatting the number for the total as "Accounting" type, which I prefer to keep if possible. The "Currency" type does not display this pesky dash, but places the dollar sign smack against the figure for the total, so I would prefer to not use that method. Is there some way to have the cell where the total is to be displayed simply left blank until it has valid data to process?

Here's the formula I am using:

Please Login or Register  to view this content.

Thanks for any help you can offer.
[/SIZE][/FONT]

Not sure why you're still using the OFFSET function (I believe that had been replaced earlier today),:

=IF(COUNT(J11:J119),SUM(J11:J119),"")

Or if 0 is never "valid" result you could use a custom format in J120 to have 0 display as blank, eg: _-[$$-409]* #,##0.00_ ;_-[$$-409]* -#,##0.00 ;;

Thanks for your reply. Yeah, we replaced the OFFSET in a function earlier for a different part of the sheet. I have only been using Excel for about 24 hours at this point, so most of this is still Greek to me; thankfully this sheet is nearly finished. I did try your formula =IF(COUNT(J11:J119),SUM(J11:J119),"") and it works fine (removes the dash, totals correctly) until I add new rows, which is something that will happen often. New rows don't seem to get calculated in. Can your formula be modified to adjust itself for the addition of new rows?

Thanks again so very much for your help.

I would probably go for something like:

Please Login or Register  to view this content.

this seems more convoluted (and is) than OFFSET but OFFSET is a Volatile function meaning the function is calculated possibly more than is required - without knowing how big your model is this may not be a big deal but generally speaking where you can avoid Volatiles you should (IMO I should add).

Works Perfectly. This is exactly the solution I was hoping for. Thank you (again) so very much for your help.[/SIZE][/FONT]