+ Reply to Thread
Results 1 to 8 of 8

TRUE/FALSE Based on multiple criteria

  1. #1
    Registered User
    Join Date
    07-05-2010
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    40

    TRUE/FALSE Based on multiple criteria

    Hey Guys,

    I found myself a bit in a pickle… I need to do a simple TRUE/FALSE/NOT APPLICABLE check but based on a number of defined conditions.

    The background to it is:

    1) We are producing a set of components
    2) Each of the components has a unique reference (Column A)
    3) For each of the components there are either 1, 2 or 3 associated drawings (Column B)
    4) Each of the drawings has a status (Column C)
    5) I need to perform a check to see if all drawings associated to the component are at status “E – Exported”. Right now it is a manual and a very laborious process 
    6) The conditions are as follow:
    a. If all of the drawings (B) associated to the component (A) have status “E – Exported”, than the check = TRUE
    b. If some, but not all of the drawings (B) associated to the component (A) have status “E – Exported”, than the check = FALSE
    c. If there are other statuses but no status “E – Exported”, than the check = N/A

    I have attached an example of a number of possible conditions for which I need the formula to spit out N/A, TRUE or FALSE.

    Hope all this blubber makes sense (I included some notes in the file).

    To be perfectly honest, I don’t even know where to start! Your help would be greatly appreciated!

    Thanks,
    Krzys (bond002)
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: TRUE/FALSE Based on multiple criteria

    Please Login or Register  to view this content.

  3. #3
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: TRUE/FALSE Based on multiple criteria

    Got the solution...
    it was a bit tricky.. but I managed..

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


    Check the attachment...

    Don't forget to click *
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-05-2010
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: TRUE/FALSE Based on multiple criteria

    Hey guys!

    Thanks a million! Both of the solutions worked - I will go with @Vikas_Gautam - less VBA in the spreadsheet = more people can use it

    Cheers,
    Krzys

  5. #5
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: TRUE/FALSE Based on multiple criteria

    Thanks for the reputation.. and for reply...

  6. #6
    Registered User
    Join Date
    07-05-2010
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: TRUE/FALSE Based on multiple criteria

    Vikas,

    I've ran into a small problem when applying your solution - when there is other status than A and E, the formula return a True value when it should be false. Do you think you could help me with that?

    Please see attached.

    Thanks,
    bond002
    Attached Files Attached Files

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

    Re: TRUE/FALSE Based on multiple criteria

    =IF(A2="","",
    IF(COUNTIFS($A$2:$A$18,A2,$C$2:$C$18,"<>E - Exported")=COUNTIF($A$2:$A$18,A2),"n/a",
    COUNTIFS($A$2:$A$18,A2,$C$2:$C$18,"E - Exported")=COUNTIF($A$2:$A$18,A2)))
    "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

  8. #8
    Registered User
    Join Date
    07-05-2010
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: TRUE/FALSE Based on multiple criteria

    Perfect! Thanks a million!

+ 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] Multiple TRUE,FALSE criteria
    By quibilty in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-25-2013, 11:47 PM
  2. [SOLVED] Determining True/false based on multiple criteria
    By byeong in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-27-2012, 12:04 PM
  3. [SOLVED] Excel 2007 : Determining TRUE or FALSE based on multiple values
    By BarnesB in forum Excel General
    Replies: 5
    Last Post: 05-30-2012, 04:46 PM
  4. Auto filter based on true / false criteria
    By Rossce in forum Excel General
    Replies: 4
    Last Post: 04-27-2012, 10:44 AM
  5. Deleting rows based on True/False Criteria
    By CatherineN in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-15-2007, 02:40 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