How do I sum a series of cells only if there is a number in any of the cells (otherwise leave it blank)? The current formula I am using keeps returning a zero when there is no number in any of the cells it is referencing.
Try this formula
COUNT counts numbers so you only get a sum if that count is > 0, otherwise you get a blank
use your actual range in place of "Range"
If there are no zero values on the sheet that you want explicitly displaying, then there is a per-worksheet option to hid zero values. The exact place to find this option varies between versions of Excel so you'll need to tell us which vesrion you are using (or search its help for "zero values" or similar).
If there are cells that might contain a zero which you do want shown, then Barry's answer would work, though instead of adding complication to your formulas you might be better off using a custom number format (in the format cells dialogue). When setting a custom format you can specify separate formats for positive numbers, negative numbers, zeros and text. For instance the following:
Something I like to do with zero values when they might be visually distracting is leave them there but grey them out, so you can see a calculation has been done and resutled in zero rather than not seeing the difference between an empty cell and a zero value. Something like
See http://office.microsoft.com/en-gb/excel-help/create-or-delete-a-custom-number-format-HP001216503.aspx for more details - the feature is quite flexible and offers some options not available through other conditional formatting features. That page misses listing the colour palette as used in my grey-out example, but you can find that elsewhere on the web such as http://dmcritchie.mvps.org/excel/colors.htm.