+ Reply to Thread
Results 1 to 6 of 6

Ignore formula and return zero if cell is blank

  1. #1
    Registered User
    Join Date
    06-22-2018
    Location
    Doncaster, UK
    MS-Off Ver
    2010
    Posts
    3

    Ignore formula and return zero if cell is blank

    Hi guys,

    It seems like this should be pretty basic, but I've been googling since yesterday with no luck.

    A B C
    1 10 20 30
    2 20 0
    3 20 5 25

    So, C1=A1+B1. If either column A or B is blank, I would like column C to ignore the formula and return a result of zero as in row 2. Can anyone help?

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Ignore formula and return zero if cell is blank

    Try it this way in C1:

    =IF(OR(A1="",B1=""),0,A1+B1)

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    06-22-2018
    Location
    Doncaster, UK
    MS-Off Ver
    2010
    Posts
    3
    Thanks for the fast reply!

    I'm still struggling though. I've attached a sheet, if you could point out where I'm going wrong?
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Ignore formula and return zero if cell is blank

    You don't have blanks in column B - they contain zeros. Put this formula in D3:

    =IF(OR(B3=0,C3=0),0,B3-C3)

    Then copy down. The file that you attached had calculation options set to Manual, so set them to Automatic using the menu option Formulas | Calculations | Calculation Options (or just press F9 if you want it to remain as Manual).

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    06-22-2018
    Location
    Doncaster, UK
    MS-Off Ver
    2010
    Posts
    3

    Re: Ignore formula and return zero if cell is blank

    Bah, I should have known that. Works perfect now, thank you!

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Ignore formula and return zero if cell is blank

    Glad to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Ignore Formula if Cell is blank
    By Vulpecula in forum Excel General
    Replies: 2
    Last Post: 03-06-2018, 01:36 AM
  2. Return Cell with content - ignore blank cells
    By MacoF in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-15-2015, 04:29 AM
  3. Replies: 3
    Last Post: 09-04-2014, 01:41 PM
  4. [SOLVED] Return non-blank cells from range AND ignore cells with only formula w/o results
    By grafitti in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-06-2014, 08:15 AM
  5. Ignore Cell if it is blank (but still has formula)
    By thelegazy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-25-2013, 12:30 PM
  6. Formula (SUM & IF) to ignore a blank cell?
    By DORourke in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-24-2013, 06:48 PM
  7. How? Ignore cell if blank in an IF formula.
    By n2lectual in forum Excel General
    Replies: 7
    Last Post: 03-26-2008, 03:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1