Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Sum with N/A's

Latest post Tue, Feb 17 2015 6:20 AM by mattgdunn. 4 replies.
  • Fri, Oct 3 2008 2:48 PM

    • david m
    • Top 25 Contributor
    • Joined on Sun, Sep 7 2008
    • Colorado Springs
    • Posts 49
    • Points 757

    Sum with N/A's

    Is there a command I can use to sum a column of Vlookup's where some of the cells come back N/A. Thanks

    • Post Points: 37
  • Sat, Oct 4 2008 12:40 AM In reply to

    • mruddyiii
    • Not Ranked
    • Joined on Sat, Oct 4 2008
    • Posts 1
    • Points 21

     

    Here is a little trick that works for me;

    =IF(ISNA(VLOOKUP(G3,C4:D6,2,FALSE)),0,VLOOKUP(G3,C4:D6,2,FALSE))

    ISNA (command) returns a TRUE value if your vlookup = NA

    So if it is NA then display a ZERO otherwise return the vlookup function.

    Then your sum function won’t be affected.

    Mike

    • Post Points: 21
  • Sat, Oct 4 2008 8:59 PM In reply to

    • david m
    • Top 25 Contributor
    • Joined on Sun, Sep 7 2008
    • Colorado Springs
    • Posts 49
    • Points 757

    Thanks that worked great!

    • Post Points: 21
  • Sun, Oct 5 2008 12:07 PM In reply to

    Hi

     

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

     

    will sum column A excluding the #N/A errors.

     

    Richard

    • Post Points: 5
  • Tue, Feb 17 2015 6:20 AM In reply to

    • mattgdunn
    • Not Ranked
    • Joined on Tue, Feb 17 2015
    • Posts 1
    • Points 5

    I think the simplest solution to your issue is to insert the following formula (which, if you use "" in the second part of the formula will return a blank or you could use a 0 instead):

     

    = iferror (YourVlookupFormula, WhatToSayInsteadOf#N/A)

    That way, you can sum up the column and there are no #N/A results to mess up the sum formula

     

    (source: http://www.excelvlookuphelp.com/i-do...ue-isnt-found/) 

     

    • Post Points: 5
Page 1 of 1 (5 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.