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,199
  • 39
  • 117
  • 171
Pauline
  • 11
  • 2
  • 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 …](http://superuser.com/q/515932/150988) and [(do something in) Excel … if (selected) cells … contain a value higher than 0](http://superuser.com/q/543790/150988). – Scott - Слава Україні Mar 12 '13 at 22: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
  • 2,473
  • 10
  • 29
  • 45
sgryzko
  • 121
  • 7
  • 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
  • Received*Returned*In Stock*Checked Out*Checked out by: *Total These are my columns. I need to be able to add rcv & Returned to sum up in column marked "In stock". If rcv & Ret are zero then zero should be in "In stock" Column. If no data is entered in these two columns I don't want to see a zero in the "In stock" Column. Same for total column. Total column is "in stock minus "Checked out column" same scenario if there is no data entered I don't want a zero showing up. Only want a zero when I enter a zero in either column to designate I don't have that item. – Pauline Mar 12 '13 at 22:29
  • What if Received is blank but there is a value under Returned? (this will decide if you should be using the AND or the OR formula) – sgryzko Mar 12 '13 at 23:02
  • Do you mean like [this](http://i.stack.imgur.com/xOWYj.jpg)? If not, please attach to your question an example of your desired behavior. – sgryzko Mar 12 '13 at 23:07
  • I don't see how the "AND" formula in my answer would not do what you are wanting. I think what you need is to read the documentation on [IF](http://office.microsoft.com/en-ca/excel-help/if-HP005209118.aspx), [OR](http://office.microsoft.com/en-ca/excel-help/or-function-HP010342743.aspx?CTT=1), and [SUM](http://office.microsoft.com/en-ca/excel-help/sum-function-HP010062464.aspx). – sgryzko Mar 12 '13 at 23:10
  • Ok it worked you're awesome, now how do I subtract my in stock from my checked out column. Same formula? – Pauline Mar 12 '13 at 23:44
  • @Pauline Read the documentation, try it out, and report back if you have any problems with it. – sgryzko Mar 13 '13 at 21:57
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,484
  • 1
  • 15
  • 19
-1

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

Kruug
  • 5,222
  • 3
  • 22
  • 30