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

Canadian Luke
  • 24,640
Pauline
  • 11

3 Answers3

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
Donald Duck is with Ukraine
  • 2,629
sgryzko
  • 121
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.

chuff
  • 3,534
-1

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

Kruug
  • 5,250