How to sum if there are values in range of cells and otherwise leave blank

Question

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.

Answer 1

Try this formula

=IF(COUNT(Range),SUM(Range),"")

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"

Answer 2

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: 0;[Red]-0; specifies that positive numbers have no decimal places shown, negatives are rounded too and have - prepended and are coloured red, and zeros are left blank (as there is nothing after the second ;. Without the second ; (i.e. 0;[Red]-0) excel assumes you don't want anything special done with the zeros and uses its default behaviour (usually showing them).

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 0;[Red]-0;[Color16]0 does the trick, or you could even replace the zero with something else like "n/a" or "-" (using 0;[Red]-0;[Color16]"n/a" or 0;[Red]-0;[Color16]"-" respectively for those two examples).

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.