Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
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
add comment

3 Answers

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
share|improve this answer
    
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. –  interrobang 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? –  interrobang Mar 12 '13 at 21:49
show 7 more comments

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.