+ Reply to Thread
Results 1 to 6 of 6

Two cells which have formulas stopping error messages show an error when added !

  1. #1
    Forum Contributor
    Join Date
    09-17-2009
    Location
    Torquay, England
    MS-Off Ver
    Excel 2013
    Posts
    250

    Two cells which have formulas stopping error messages show an error when added !

    Long title, sorry.

    Please see the attached sheet. I am trying to add together two figs which are linked to calculations which have formula built in to stop error messages when there is a 0 / 0 = #value type error.

    However when these two cells are added, if the cells are blank I get an error message. And if only one cell has a value, I get "" with my existing formula.

    Could anyone let me know what I need to do to get a result of 7 if for example cell A4 = "" + B4 =7. At the moment my formula shows "" in the sum total of these cells

    Any help appreciated, an example attached.

    Thanks
    Attached Files Attached Files
    Last edited by Gooford; 11-10-2009 at 06:54 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Two cells which have formulas stopping error messages show an error when added !

    I would suggest you revert to SUM, ie

    C5: =IF(COUNT(A5:B5),SUM(A5:B5),"")

    (for an explanation as to why SUM will work and + will not see: http://www.excelforum.com/excel-gene...no-result.html ... bear in mind a Null ("") is a text string)

  3. #3
    Forum Contributor
    Join Date
    09-17-2009
    Location
    Torquay, England
    MS-Off Ver
    Excel 2013
    Posts
    250

    Re: Two cells which have formulas stopping error messages show an error when added !

    Hi,

    Sorry on my original document the columns that need to be added are not adjacent to each other, they are E and G, so I cant us E5:G5 as there is a value in F.

    Is there an alternative?

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Two cells which have formulas stopping error messages show an error when added !

    on which basis:

    =IF(COUNT(E5,G5),SUM(E5,G5),"")

  5. #5
    Forum Contributor
    Join Date
    09-17-2009
    Location
    Torquay, England
    MS-Off Ver
    Excel 2013
    Posts
    250

    Re: Two cells which have formulas stopping error messages show an error when added !

    Spot on thanks. just a comma eh?

    Great help as usual

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Two cells which have formulas stopping error messages show an error when added !

    Yes certain functions like SUM, COUNT, COUNTA etc can accept a variable number of inputs (delimited by comma) whereas others like say COUNTBLANK etc can not (single contiguous range only) - if in doubt check out the XL Help file - they're generally (though not always) pretty good.

    (also as you type in the formula you should get a rough idea of what can be added by means of the "tooltip/hint" text that appears).

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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