+ Reply to Thread
Results 1 to 5 of 5

Is it possible to have a range in an AND statement?

  1. #1
    Registered User
    Join Date
    06-19-2014
    Location
    Midlands, UK
    MS-Off Ver
    2010
    Posts
    58

    Is it possible to have a range in an AND statement?

    Hi All

    I have a formula that I need to examine a range of criteria using IF & AND to check results. I have several columns and can create multiple AND logical criteria but this takes a huge amount of time as there are over 30 columns, and will grow to many more.

    The formula I would use and know works is:

    =if(and(b1="No",b2="No"),"Not Started",In Progress")

    b1 onwards could eventually reach in excess of b100. (RSI City)! )

    What I though I would do is

    =if(and(b1:b100="No"),"Not Started","In Progress")

    but this always returns a #value! error.

    Probably something simple but can't get the darn thing to work.

    As always, thanks in advance for any help you can provide.

    TexasKersh

  2. #2
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: Is it possible to have a range in an AND statement?

    You can enter your formula as CSE (array formula, using Control+Shift+Enter rather than just enter) and it works.
    ________________________________________________________
    If your problem is solved, update the thread as SOLVED: Go to the top of the first post-Select Thread Tools-Select Mark thread as Solved OR - Go to the first post - Click edit- Click Advance- Just below the word "Title:" you will see a dropdown with the word No prefix.- Change to Solve- Click Save.

    Show your gratitude to the person who helped you solving your problem by clicking on star button at the bottom of such post.

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,683

    Re: Is it possible to have a range in an AND statement?

    Try this...

    =IF(COUNTIF(B1:B100,"No"),"Not Started","In Progress")


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Registered User
    Join Date
    06-19-2014
    Location
    Midlands, UK
    MS-Off Ver
    2010
    Posts
    58

    Re: Is it possible to have a range in an AND statement?

    This worked perfectly. Thanks Haripopuri

    Know it would be something simple !

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,683

    Re: Is it possible to have a range in an AND statement?

    Not Array.....

    =IF(COUNTIF(B1:B100,"No")=ROWS(B1:B100),"Not Started","In Progress")

+ 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] IF Statement Range
    By Mark9 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-23-2014, 11:24 AM
  2. [SOLVED] If then statement using range
    By zeroaxs in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-07-2013, 12:21 PM
  3. If statement with a range and true/false statement
    By aarleblanc in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-20-2012, 06:34 PM
  4. If Statement in a Range
    By new@this in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-18-2007, 02:34 PM
  5. Use a range name in VB with IF Then Else statement
    By spydor in forum Excel General
    Replies: 1
    Last Post: 01-05-2006, 09:25 PM

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