Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Sep 2005
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    #N/A's in cells (Excel 2000)

    Hi,

    I've linked a couple of spreadsheets and have got alot of #N/A's in certain cells. I'm trying to add all of the column together but because of the #N/A's these aren't totalling, is there any way around this problem?

    Thanks for any help
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: #N/A's in cells (Excel 2000)

    Welcome to Woody's Lounge!

    The following formula, entered as an array formula (i.e. confirmed with Ctrl+Shift+Enter instead of just Enter), will add all numeric entries in A1:A1000:

    =SUM(IF(ISNUMBER(A1:A1000),A1:A1000))

  3. #3
    New Lounger
    Join Date
    Sep 2005
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #N/A's in cells (Excel 2000)

    Hi Hans,

    Thanks for the quick reply! I've just tried that formula but I just end up with 0 in the total for some reason?

    Thanks again
    Andy

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: #N/A's in cells (Excel 2000)

    1) did you make sure you confirmed with ctrl-shift-enter not just enter?

    2) are you sure they are numbers and not text that looks like numbers?

    Steve

  5. #5
    New Lounger
    Join Date
    Sep 2005
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #N/A's in cells (Excel 2000)

    Thanks Steve - it was the ctrl-shift-enter bit i'd not done - all ok now.

    Cheers for your help!

    Andy

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: #N/A's in cells (Excel 2000)

    Another way is to use the ISNA function, ie replace the formula that is causing the #N/As - [yours], in my example below - with the following:

    =if(ISNA([yours]),0,[yours])

    That way, if the result *would* have been #N/A it's replaced with a zero.

    HTH
    Beryl M


  7. #7
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #N/A's in cells (Excel 2000)

    Try,

    =SUMIF(A1:A100,"<>#N/A")

    or rid the cells of the #N/A and just use SUM.

Posting Permissions

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