You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

📰 Apple Fitness+ unveils an exciting lineup of new ways to stay active and mindful in 2025

Offerings include new programs for strength, pickleball, yoga, and breath meditation, and a new collaboration with Strava. Learn more >

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

I don't want to se any zeros in my spreadsheet in Numbers

If the sum of an argument is zero, I want to see an empty field, not the number zero. Any suggestions?

Mac mini, macOS 13.5

Posted on Sep 23, 2023 9:09 AM

Reply
Question marked as Top-ranking reply

Posted on Sep 23, 2023 1:32 PM

You have changed the question and that changes the answer. To answer your new question, and assuming "nothing" means a cell is blank:


Say that the three numbers to be added are in cells B2, B3, and B4.


One formula would be =IF(OR(B2="",B3="",B4=""),"", B2+B3+B4)

This one requires all three cells to have numbers before you get any answer


Another formula would be =IF(AND(B2="",B3="",B4=""),"", B2+B3+B4)

This one will give an answer if any one or more of the cells contains a number


If you are actually adding more than 3 cells, at some point expanding this formula gets unwieldy and a different formula would be better.


Similar questions

8 replies
Sort By: 
Question marked as Top-ranking reply

Sep 23, 2023 1:32 PM in response to Jew2

You have changed the question and that changes the answer. To answer your new question, and assuming "nothing" means a cell is blank:


Say that the three numbers to be added are in cells B2, B3, and B4.


One formula would be =IF(OR(B2="",B3="",B4=""),"", B2+B3+B4)

This one requires all three cells to have numbers before you get any answer


Another formula would be =IF(AND(B2="",B3="",B4=""),"", B2+B3+B4)

This one will give an answer if any one or more of the cells contains a number


If you are actually adding more than 3 cells, at some point expanding this formula gets unwieldy and a different formula would be better.


Reply

Sep 23, 2023 11:01 AM in response to Badunit

Thank You Badunit but I'm a bit new to formula collections in Numbers so I probably don't understand what you mean. I'll clarify a bit: nothing+nothing+nothing=nothing but 5+5-10=0 that should say zero.

Could it be added to the formula that, when something is written in a certain field, the SUM command is activated?

Reply

Sep 23, 2023 5:55 PM in response to Jew2


Here is another method if you mean you are summing 98 cells in one formula, not 14 formulas each doing a row of 7 cells:


This one will give a result as soon as one cell in the range is filled in

=IF(COUNT(B5:H18)=0,"",SUM(B5:H18))


And this one requires them all to be filled in before it gives a result

=IF(COUNT(B5:H18)=ROWS(B5:H18)×COLUMNS(B5:H18),SUM(B5:H18),"")

or

=IF(COUNT(B5:H18)=98,SUM(B5:H18),"")


You can use this same general idea if you have 14 different formulas each summing the 7 cells from its row. Just change the ranges in the formulas to be the 7 cells of the current row (and you won't need the ROWS function in the one formula).

Reply

Sep 24, 2023 5:04 AM in response to Badunit

Thanks, this first code was much easier to use and it works perfectly in all my calculations in different variations.

In my case, it was:

The sum of each row is in column I.

The sum of I5-I18 is in I19 but I couldn't use that, I got an error which I did not have the knowledge to analyze. 

I had to take the sum of B5-H18 instead in I19.

But now with the new code i could use the sum of I5-I18 to I19.

It's a whole new and exciting world that opens up with coding in "Numbers".

The risk is that I become a bit "nerdy".

What I'm working with in this case is a work schedule where each row is an employee and the 7 cells in the row are days of the week for a week. It would be nice if I could go ahead with that if a cell is filled in with a number greater than 8 eg 11 then the number 3 would go to column J (overtime compensation) but if it is filled in numbers in cell Saturday or Sunday (column G or H) it goes to column K (overtime compensation weekend).

I'll see if I can figure it out. Thank you.

Reply

I don't want to se any zeros in my spreadsheet in Numbers

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.