+ Reply to Thread
Results 1 to 23 of 23

Complicated IF, AND, OR Statement

  1. #1
    Registered User
    Join Date
    06-24-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    38

    Complicated IF, AND, OR Statement

    If T4=TRUE and T23, T24, T25, T26 (any of these = true) = C10

    If T4=TRUE AND T6=TRUE and T23, T24, T25, T26 (any of these = true) = L9

    If T6=TRUE and T23, T24, T25, T26 (any of these = true) = L9

    Hope this makes sense. Thanks for help!!

  2. #2
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    650

    Re: Complicated IF, AND, OR Statement

    are you asking us to write a formula which captures all that logic? a sample workbook would most likely help explain your request. also, your title could probably be more descriptive. i'm assuming the last = in each of your three expressions is a "then" in that line's if, then statement. also, i'm assuming you want to check the first set of criteria first, and if that is not true then move on to the second and then third.

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


    untested

  3. #3
    Registered User
    Join Date
    06-24-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    38

    Re: Complicated IF, AND, OR Statement

    Yes - and thank you. The formula you provided works great except for one problem. when t4 and t6 are both true it is showing the value in c10 instead of L9. Thank you very much for your help!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,898

    Re: Complicated IF, AND, OR Statement

    Do those cells actually contain the word TRUE, or what is in them?
    If T4=TRUE and T23, T24, T25, T26 (any of these = true) = C10
    If T4=TRUE AND T6=TRUE and T23, T24, T25, T26 (any of these = true) = L9
    If T6=TRUE and T23, T24, T25, T26 (any of these = true) = L9
    =IF(AND(T4=TRUE,countif(T23:T26,True)>0),C10,IF(AND(T6=TRUE,countif(T23:T26,True)>0),L9,IF(AND(T4=TRUET6=TRUE,countif(T23:T26,True)>0),"")
    Or maybe
    =IF(countif(T23:T26,True>0),IF(T6=TRUE,L9,C10),"")
    your 2nd 2 rules will both give L9 if T6 is TRUE, so rule 2 is kinda redundant
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    06-24-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    38

    Re: Complicated IF, AND, OR Statement

    Yes - the cells are linked to check boxes.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,898

    Re: Complicated IF, AND, OR Statement

    OK, so did you try the suggestions?

  7. #7
    Registered User
    Join Date
    06-24-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    38

    Re: Complicated IF, AND, OR Statement

    Working on your suggestion right now. thanks!

  8. #8
    Registered User
    Join Date
    06-24-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    38

    Re: Complicated IF, AND, OR Statement

    So it works like the formula that was posted right before. So if I check T4 is returns the correct value. If I check T6 it returns the correct value. If I have them both checked it returns an incorrect value...it is returning C10 instead of L9

  9. #9
    Registered User
    Join Date
    06-24-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    38

    Re: Complicated IF, AND, OR Statement

    and if nothing is checked can it report a 0 instead of FALSE?

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,898

    Re: Complicated IF, AND, OR Statement

    Perhaps its time to upload a small sample workbook for us, so that we can stop guessing

  11. #11
    Registered User
    Join Date
    06-24-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    38

    Re: Complicated IF, AND, OR Statement

    Excel Forum.xlsx

    Did it attach?

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,898

    Re: Complicated IF, AND, OR Statement

    Thanks.

    Are we talking about testing for C4:C6 on its own and C9:C12 on its own, or C4:C12?

    Also, why use check boxes, why not just have a cell to enter an x or something?

  13. #13
    Registered User
    Join Date
    06-24-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    38

    Re: Complicated IF, AND, OR Statement

    I inherited the worksheet...just trying to make it work with the checkboxes.

    If test 1 is checked (true) and 1 or more of the 'Choices' is true then it would return the value $100.

    If test 3 is checked (true) and 1 or more of the 'Choices' is true then it would return the value $200.

    If test 1 and test 3 are both checked (true) and 1 or more of the 'Choices' is true then it would return the value $200.

    If Test 1 or Test 3 are checked and no choice is selected = 0

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,898

    Re: Complicated IF, AND, OR Statement

    Yes, that is the same rules you have in your workbook...
    Are we talking about testing for C4:C6 on its own and C9:C12 on its own, or C4:C12?

  15. #15
    Registered User
    Join Date
    06-24-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    38

    Re: Complicated IF, AND, OR Statement

    I guess I don't understand what you are asking? sorry.

  16. #16
    Registered User
    Join Date
    06-24-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    38

    Re: Complicated IF, AND, OR Statement

    what is the difference between the 2 options: Are we talking about testing for C4:C6 on its own and C9:C12 on its own, or C4:C12?

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,898

    Re: Complicated IF, AND, OR Statement

    Sorry, misunderstood, try this...
    =IF(AND(C6,COUNTIF($C$9:$C$12,"TRUE")),G5,IF(AND(C4,COUNTIF($C$9:$C$12,"TRUE")),F5,""))

  18. #18
    Registered User
    Join Date
    06-24-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    38

    Re: Complicated IF, AND, OR Statement

    PERFECT!!! Thank you so much!!

  19. #19
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,664

    Re: Complicated IF, AND, OR Statement

    As a possible alternative try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,898

    Re: Complicated IF, AND, OR Statement

    Glad we got what you needed, thanks for the feedback

  21. #21
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,524

    Re: Complicated IF, AND, OR Statement

    =MAX(INDEX((C4:C6=TRUE)*{100;0;200}*(COUNTIF(C9:C12,TRUE)>0),0))
    Try this formula
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  22. #22
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,664

    Re: Complicated IF, AND, OR Statement

    Quote Originally Posted by nflsales View Post
    =MAX(INDEX((C4:C6=TRUE)*{100;0;200}*(COUNTIF(C9:C12,TRUE)>0),0))
    Try this formula
    Good one, nflsales! Thanks for sharing.

  23. #23
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    650

    Re: Complicated IF, AND, OR Statement

    disregard - i didn't see the second page of responses. my solution was already covered.
    Last edited by simarui; 12-10-2014 at 04:16 PM.

+ 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] Need Help with Complicated IF Statement with Many Variables
    By loriahein in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 03-14-2014, 03:08 PM
  2. [SOLVED] Help writing a complicated IF statement
    By outdoorsaddix in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-15-2013, 10:28 PM
  3. Complicated If/And/Or statement
    By Tideronthestorm in forum Excel General
    Replies: 4
    Last Post: 12-04-2012, 12:54 PM
  4. Need If, then statement - kind of complicated
    By MidCon in forum Access Programming / VBA / Macros
    Replies: 2
    Last Post: 05-13-2009, 12:39 PM
  5. Complicated If statement?
    By aposatsk in forum Excel General
    Replies: 0
    Last Post: 08-03-2006, 01:50 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