+ Reply to Thread
Results 1 to 6 of 6

If #N/A, then return a blank cell, not a null text string

  1. #1
    Registered User
    Join Date
    04-15-2014
    Location
    Toms River, NJ
    MS-Off Ver
    Excel 2010
    Posts
    37

    If #N/A, then return a blank cell, not a null text string

    I have this vlookup formula:

    =IF(ISERROR(VLOOKUP(AS34,Inputs!$A$19:$B$30,2,0)),"",VLOOKUP(AS34,Inputs!$A$19:$B$30,2,0))

    In another cell on the same row, I have this formula:

    =IF($K$1=$M$33,COUNTA(M34),IF($K$1=$N$33,COUNTA(M34:N34),IF($K$1=$O$33,COUNTA(M34:O34),IF($K$1=$P$33,COUNTA(M34:P34),IF($K$1=$Q$33,COUNTA(M34:Q34),IF($K$1=$R$33,COUNTA(M34:R34),IF($K$1=$S$33,COUNTA(M34:S34),IF($K$1=$T$33,COUNTA(M34:T34),IF($K$1=$U$33,COUNTA(M34:U34),IF($K$1=$V$33,COUNTA(M34:V34),IF($K$1=$W$33,COUNTA(M34:W34),IF($K$1=$X$33,COUNTA(M34:X34),IF($K$1=$Y$33,COUNTA(M34:Y34),IF($K$1=$Z$33,COUNTA(M34:Z34),IF($K$1=$AA$33,COUNTA(M34:AA34),IF($K$1=$AB$33,COUNTA(M34:AB34),IF($K$1=$AC$33,COUNTA(M34:AC34),IF($K$1=$AD$33,COUNTA(M34:AD34),IF($K$1=$AE$33,COUNTA(M34:AE34),IF($K$1=$AF$33,COUNTA(M34:AF34),IF($K$1=$AG$33,COUNTA(M34:AG34),IF($K$1=$AH$33,COUNTA(M34:AH34),IF($K$1=$AI$33,COUNTA(M34:AI34),IF($K$1=$AJ$33,COUNTA(M34:AJ34),IF($K$1=$AK$33,COUNTA(M34:AK34),IF($K$1=$AL$33,COUNTA(M34:AL34),IF($K$1=$AM$33,COUNTA(M34:AM34),IF($K$1=$AN$33,COUNTA(M34:AN34),IF($K$1=$AO$33,COUNTA(M34:AO34),IF($K$1=$AP$33,COUNTA(M34:AP34),IF($K$1=$AQ$33,COUNTA(M34:AQ34))))))))))))))))))))))))))))))))

    Because the vlookup returns a null text string, I cannot use COUNTA.

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If #N/A, then return a blank cell, not a null text string

    Not sure what that long formula is supposed to be doing but you can replace COUNTA with COUNTIF(range,"?*") to count TEXT values but exclude formula blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: If #N/A, then return a blank cell, not a null text string

    Sorry, a formula CANNOT return a blank cell. Period.
    COUNTA will always consider it NOT blank, simply because it contains a formula.
    Regardless of what the formula returns.

    Now, for reasons I've never understood...
    The countBLANK function is the opposite.
    It will count a formula blank "" as blank.

    I know it sucks, but you could replace all your counta() functions with Columns()-Countblank()

    COUNTA(M34) becomes COLUMNS(M34)-COUNTBLANK(M34)
    COUNTA(M34:N34) becomes COLUMNS(M34:N34)-COUNTBLANK(M34:N34)
    etc..

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: If #N/A, then return a blank cell, not a null text string

    Your original formula could be simplified considerably:

    =COUNTA(M34:INDEX(M34:AQ34,MATCH(K1,M33:AQ33,0)))

    To account for the formula blanks..

    =MATCH(K1,M33:AQ33,0)-COUNTBLANK(M34:INDEX(M34:AQ34,MATCH(K1,M33:AQ33,0)))

  5. #5
    Registered User
    Join Date
    04-15-2014
    Location
    Toms River, NJ
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: If #N/A, then return a blank cell, not a null text string

    Jonmo1, you are unbelievable. Thank you sooooo very much. I am still new at the index formula.

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: If #N/A, then return a blank cell, not a null text string

    Glad to help, thanks for the feedback.

+ 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. [SOLVED] Omitting Null Values from a text string
    By Skycap in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-03-2013, 02:32 AM
  2. [SOLVED] IF range of cells contains string of text, return contents of cell where string is found
    By nobodyukno in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-03-2013, 09:56 AM
  3. [SOLVED] Find partial text string within another text string return original text into cell.
    By mikey42979 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-17-2013, 02:58 PM
  4. Can a function return a Null (blank ) value? Maybe a custom functi
    By colin_e in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-16-2006, 10:40 AM
  5. Replace null string with blank cell
    By gjcase in forum Excel General
    Replies: 2
    Last Post: 08-09-2005, 09:13 AM

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