+ Reply to Thread
Results 1 to 5 of 5

Problem with Sum function

  1. #1
    Registered User
    Join Date
    04-24-2004
    Location
    INDIA
    MS-Off Ver
    2013
    Posts
    70

    Problem with Sum function

    I am using a vlookup function and due to data un-availability there is an error (#N/A) shows in a particular cell. I have hidden this error using ISERROR but when I sum these columns the same error (#N/A) appears in Total column.

    How can I eliminate this error and get actual values.

    Thanks.

    Shubhkaran

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440
    Hi,

    Use this array formula

    =SUM(IF(ISNUMBER(range),range,""))

    Hold down CTRL & SHIFT while pressing ENTER to confirm this
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    04-24-2004
    Location
    INDIA
    MS-Off Ver
    2013
    Posts
    70
    Thanks for prompt reply.

    what should I give in range

    =SUM(IF(ISNUMBER(C5:C16),range,""))

    shubhkaran

  4. #4
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440
    Same range

    =SUM(IF(ISNUMBER(C5:C16),C5:C16,""))

    Dave

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    You could try

    =SUMIF(C5:C16,"<>#N/A")

+ 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