+ Reply to Thread
Results 1 to 5 of 5

If statement logical test range cells

  1. #1
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    If statement logical test range cells

    How can I construct an IF statement to look over a range of cells in the logical test? I tried the following but it will not work.

    I would like the statement to go if any cell in the range c21:j21 is equal to N/A then spit back 2 if not then 1

    =IF(C21:J21="n/a",2,1)
    Last edited by rhudgins; 12-07-2010 at 04:51 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: If statement logical test range cells

    =IF(COUNTIF(C21:J21,"n/a"),2,1)

    or

    =IF(ISNUMBER(MATCH("n/a",C21:J21,0)),2,1)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581

    Re: If statement logical test range cells

    Hi,

    Are you looking for "n/a" as a text field, or #N/A the error message?

    For "n/a":
    = IF(SUMPRODUCT(--(C21:J21="n/a"))>0,2,1)

    For #N/A:
    = IF(SUMPRODUCT(--ISERROR(C21:J21))>0,2,1)

    S
    ------------------------------------------------------------------------------------------
    If you need no more help on the current problem, please mark it as "Solved". It saves time
    as many of us will look at threads if they are not marked as "Solved".

    The instructions on how to do this are found in the Forum Rules thread that is at top of every forum.
    (Currently you'll have to look at point #9.)
    ------------------------------------------------------------------------------------------

  4. #4
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,377

    Re: If statement logical test range cells

    Edit - sorry misread post and i see you have had some responses in the the i was responding

    try this

    =COUNTIF(C21:J21,"N/A")
    Last edited by Blake 7; 12-07-2010 at 04:43 PM.
    Blake 7

    If your question has been answered, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

    If any member's response has helped to solve your problem, please consider using the scales icon on the top right of their post to show your appreciation.

    http://failblog.org/

  5. #5
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: If statement logical test range cells

    Thaks for all of the suggestions

+ 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