1

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

2

3 Answers 3

2

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:

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
11
  • But when I type in data the zero does not appear if it equals to zero.
    – Pauline
    Mar 12, 2013 at 21:11
  • I believe you want the second formula with an AND Mar 12, 2013 at 21:13
  • @Pauline I've updated my answer. Let me know if this is what you're looking for.
    – sgryzko
    Mar 12, 2013 at 21:16
  • Sorry gives me an error as insufficient formula :(
    – Pauline
    Mar 12, 2013 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, 2013 at 21:49
0

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.

-1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .