Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Contains a criteria (2k)

    Now I know this is probably a basic question, but I just can't remember how to get a True/False result when a range contains/does not contain a specified criteria.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Contains a criteria (2k)

    The following returns TRUE if A1 contains the value "Criteria", FALSE if it does not :

    =A1="Criteria"

    Andrew C

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Contains a criteria (2k)

    Nope, that just brings the #VALUE error. I'll give an example;
    A1 has "Overdue"
    B1 has "Overdue"
    C1 has "Due"
    D1 has "OK"

    In E1 I'd like it to look in A11 & if the range has "Overdue" in it at all, return a value of "True" or "False" if there isn't.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Contains a criteria (2k)

    Sorry, I did not pick up on the Range aspect.

    Try

    =A11="Overdue", but when you enter it press Ctrl-Shift-Enter instead of Enter, as it is an array formula. It should be automatically enclosed in brace brackets ( like : {=A11="Overdue"})

    Andrew C

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Contains a criteria (2k)

    =ISNUMBER(MATCH("Overdue",A11,0))

    or

    =COUNTIF(A11,"Overdue")>0

    Aladin
    Microsoft MVP - Excel

  6. #6
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Contains a criteria (2k)

    This looks really awful but it works, avoids all the #VALUE and #NA stuff:

    =ISNA(HLOOKUP("overdue",A11,1,FALSE))=FALSE
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Contains a criteria (2k)

    Ah, getting somewhere, but I'm getting spurious results, it seems it's only checking the first column. I've attached a sample.
    Attached Files Attached Files

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Contains a criteria (2k)

    Sorry again,

    Try <big>=IF(ISNA(MATCH("Overdue",A11,0)),FALSE,TRUE)</big>

    Andrew C

  9. #9
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Contains a criteria (2k)

    Yep that's done the trick. Thank you kindly for your prompt help, you've pulled me out of a jam, Andrew.
    Paul

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Contains a criteria (2k)

    Actually Aladin's first offering looks best to me, and DoryO's solutions should also suffice.

    Andrew

  11. #11
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Contains a criteria (2k)

    Oops, sorry peeps, I was trying things out when you posted, & then just looked at the last post after.

    BTW Aladin, I had tried the COUNTIF, but I got a count of "Overdue"s in the range.

    Either way, thanks one & all.

  12. #12
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Contains a criteria (2k)

    I prefer the MATCH formula myself.

    However, the COUNTIF formula should work too:

    =COUNTIF(A11,"Overdue")>0

    I believe you omitted the >0 bit, that's why you get a count instead of a logical value.

    Regards,

    Aladin
    Microsoft MVP - Excel

  13. #13
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Contains a criteria (2k)

    GUILTY, as charged [img]/forums/images/smilies/smile.gif[/img] That's exactly what I'd done. That's one of the caveats of formulea & code; you've GOT to be precise (I just wish I was more so).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •