+ Reply to Thread
Results 1 to 10 of 10

Multiple if isna

  1. #1
    Valued Forum Contributor
    Join Date
    11-15-2007
    Location
    England
    MS-Off Ver
    Office Pro 2003 / 2010
    Posts
    412

    Multiple if isna

    Hi,

    I want to write the following formula and I get part of it then lose another and am getting nowhere fast. Could someone help please?

    I have 2 columns of figures in Column AF and AH.

    There could be #N/A in column AF or AH or both.

    I want to put a sum in column AK2 which is along these lines (I'll write it as I would speak it)

    If the contents of both AF2 and AH2 = #N/A Then AK2 0

    That covers where boths columns have #N/A

    If the contents of AF2 = #N/A Then AK2 = AH2

    That covers where just column AF has #N/A

    If the contents of AH2 = #N/A Then AK2 = AF2

    That covers where just column AH has #N/A

    Many thanks for any help.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,246

    Re: Multiple if isna

    If the contents of both AF2 and AH2 = #N/A Then AK2 0
    That covers where boths columns have #N/A
    If the contents of AF2 = #N/A Then AK2 = AH2
    That covers where just column AF has #N/A
    If the contents of AH2 = #N/A Then AK2 = AF2
    That covers where just column AH has #N/A

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Multiple if isna

    Hello

    Perhaps

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    601

    Re: Multiple if isna

    Hi
    =IF(AND(ISERROR(AF2),ISERROR(AH2)),0,IF(ISERROR(AF2),AH2,AF2))
    Appreciate the help? CLICK *

  5. #5
    Valued Forum Contributor
    Join Date
    11-15-2007
    Location
    England
    MS-Off Ver
    Office Pro 2003 / 2010
    Posts
    412

    Re: Multiple if isna

    Thanks Guys,

    All your solutions work but I realise I have made one error they only work if there is a #N/A in the one or both cells. Can you suggest a solution?
    There are three scenarios below - I need to pick up the 4 in Scenarios 1 and 2 and return a 0 for Scenario 3.Sorry for missing this.

    Scenario 1
    Column AF2 = 4
    Column AH2 = #N/A

    Scenario2
    Column AF2 = #N/A
    Column AH2 = 2

    Scenario3
    Column AF2 = #N/A
    Column AH2 = #N/A

  6. #6
    Valued Forum Contributor
    Join Date
    11-15-2007
    Location
    England
    MS-Off Ver
    Office Pro 2003 / 2010
    Posts
    412

    Re: Multiple if isna

    Thanks Guys,

    All your solutions work but I realise I have made one error they only work if there is a #N/A in the one or both cells. Can you suggest a solution?
    There are three scenarios below - I need to pick up the 4 in Scenarios 1 and 2 and return a 0 for Scenario 3.Sorry for missing this.

    Scenario 1
    Column AF2 = 4
    Column AH2 = #N/A

    Scenario2
    Column AF2 = #N/A
    Column AH2 = 2

    Scenario3
    Column AF2 = #N/A
    Column AH2 = #N/A

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,246

    Re: Multiple if isna

    Post #2 allows for this ... "what now"

    If neither are #N/A, put the result in there.


    Regards, TMS

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Multiple if isna

    =SUMIF(AF1,"<>#n/a")+SUMIF(AH1,"<>#n/a")
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  9. #9
    Valued Forum Contributor
    Join Date
    11-15-2007
    Location
    England
    MS-Off Ver
    Office Pro 2003 / 2010
    Posts
    412

    Re: Multiple if isna

    Thanks TMS.

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,246

    Re: Multiple if isna

    You're welcome. Thanks for the rep.

+ 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] Help with multiple vlookup with if, and(isna())
    By roscoepwavetrain in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-17-2013, 01:43 AM
  2. Nested IF(ISNA) Statements - Multiple Sheets
    By Cher2332 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-18-2013, 12:47 PM
  3. Multiple VLOOKUPs with a MATCH and ISNA component (or not)
    By Sandinista in forum Excel General
    Replies: 5
    Last Post: 02-01-2012, 10:55 AM
  4. Excel 2007 : Summing multiple IF(ISNA(Vlookup))
    By jseufert in forum Excel General
    Replies: 1
    Last Post: 09-07-2011, 12:42 PM
  5. ISNA
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 11:05 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