# Need a zero in excel only when data is entered

I have a worksheet I'm working on that I need the zero to appear in the cell only when data is entered into the worksheet. When there is no data I want the cell left blank. Is this possible and if so how do I add it to the formulas below.

`=SUM(D12+E12) answer goes on cell G12`
`=SUM(F12-G12) answer goes on cell I12`

-
This is, arguably, a matter of style, but you don’t need to say `SUM(D12+E12)` and `SUM(F12-G12)` –– `D12+E12` and `F12-G12` (respectively) are equivalent. Or, instead of `D12+E12`, you could say `SUM(D12,E12)` or `SUM(D12:E12)`. The `SUM` function is particularly handy for adding rows, columns, and even rectangular blocks of numbers (e.g., `SUM(A12:Z12)`, `SUM(D1:D42)`, and `SUM(D12:Q17)`); many Excel worksheet formula functions work similarly. – Scott Mar 12 '13 at 22:07
You should be able to find all the information you need in the answers to these questions: Display blank when … and (do something in) Excel … if (selected) cells … contain a value higher than 0. – Scott Mar 12 '13 at 22:11

The following will display the sum unless it's zero. (Let's all this NOZERO)

``````=IF(SUM(D12+E12)=0, "", SUM(D12+E12))
``````

This one will only calculate the sum if both inputs are non-zero. (Let's all this AND)

``````=IF(OR(D12 = "", E12 = ""), "", SUM(D12+E12))
``````

And this one will only calculate the sum if at least one input is non-zero. (Let's all this OR)

``````=IF(AND(D12 = "", E12 = ""), "", SUM(D12+E12))
``````

These formulas are summarized in this example table.

Similar logic would apply for the "=SUM(F12-G12)" cell.

PS the following three statements are equal. I think the third one is the simplest and best, but you can take your pick.

``````=SUM(D12+F12)
=SUM(D12,F12)
=D12+F12
``````
-
But when I type in data the zero does not appear if it equals to zero. – Pauline Mar 12 '13 at 21:11
I believe you want the second formula with an AND – Brad Patton Mar 12 '13 at 21:13
@Pauline I've updated my answer. Let me know if this is what you're looking for. – sgryzko Mar 12 '13 at 21:16
Sorry gives me an error as insufficient formula :( – Pauline Mar 12 '13 at 21:32
@Pauline Can you elaborate on what you are seeing? Excel is not giving me any errors when I use any of the formulas above. Or am I misunderstanding your question? – sgryzko Mar 12 '13 at 21:49

Expanding on Kruug's answer, you can use the COUNTA function to test whether any data have been entered in the parts of the worksheet that you consider data entry areas.

For example, the following formulas would show a zero only if something was entered into the range `A1:I11` of the worksheet you are interested in:

``````  For cell G12:  =IF(COUNTA(A1:I11)>0,SUM(D12,F12),"")

For cell I12   =IF(COUNTA(A1:I11)>0,SUM(F12,-G12),"")
``````

The range you specify in the COUNTA function can be any range or groups of ranges that you want. For example, if you were concerned only with the cells directly above your two sums, then you could change the formulas to:

``````  For cell G12:  =IF(COUNTA(D1:D11,F1:F11)>0,SUM(D12,F12),"")

For cell I12   =IF(COUNTA(D1:D11,F1:F11)>0,SUM(F12,-G12),"")
``````

(I'm showing the two ranges in the COUNTA function purely as an example, since I don't know what you would consider to be data ranges.)

The only thing you shouldn't do is test for data entry anywhere in the entire worksheet, because then the range to be counted would include your sum formulas and create a circular reference error.

-

Take a look at COUNTA. This counts the number of non-empty cells in a range.

-