+ Reply to Thread
Results 1 to 10 of 10

Conditional Formula - Multiple Criteria

  1. #1
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Sefton
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    133

    Conditional Formula - Multiple Criteria

    Ok, i'll do my best to explain this - i'm working on a sensitive document, so i can only post a blank example.
    Example.xlsx

    I'm trying to make the full document easier for people to see where inputs are required. What i'm looking for here is a formula i can use within the conditional formatting to highlight row 47.
    I would like row 47 to highlight if the following are true;
    A47 is blank
    A46 > 0
    S53 > 0 or S54 > 0
    N46:N49 does not contain "OS"

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,751

    Re: Conditional Formula - Multiple Criteria

    A47 is blank
    A46 > 0
    S53 > 0 or S54 > 0
    N46:N49 does not contain "OS"

    I would use

    AND( $A47 = "", $A46 > 0 OR( $S53 > 0, $S54 > 0), Countif($N46:$N49, "OS")=0 )
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Sefton
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    133

    Re: Conditional Formula - Multiple Criteria

    That doesn't look to have worked, because with "OS" appearing in N46, the highlight remains

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,751

    Re: Conditional Formula - Multiple Criteria

    This
    Countif($N46:$N49, "OS")=0
    should return a False

    can you put

    =Countif($N46:$N49, "OS")

    into a cell - What number does it return - should be 1 if you have OS in N46

    then try
    =Countif($N46:$N49, "OS")=0

    and that should return FALSE

  5. #5
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Sefton
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    133

    Re: Conditional Formula - Multiple Criteria

    right, i think i've figured out why that doesn't work. It does if OS is the only input, however there may also be numbers in N46:N49 - if there are other entries in then the formula doesn't work as intended.

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,751

    Re: Conditional Formula - Multiple Criteria

    ok, what do you want to do if other entries

    whats the rule for N46 to N49 ?

  7. #7
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Sefton
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    133

    Re: Conditional Formula - Multiple Criteria

    N46 to N49 should only contain numbers, comas and "OS1" or "OS2". No spaces, no other text.
    The other entries should not have any bearing on this highlight. I'm only interested if someone has entered data into the smaller table and not related it in the larger table.

    If you look at my example attachment, N52 (where i've input 17) would say OS, and N53 + N54 would be 1 or 2. This is then related in the larger table as OS1 and OS2 (elsewhere in the full document). However there could be data in rows 53 and 54 but not in S53 or S54 - so i only require the highlight if S54 or S54 contain data AND OS1 or OS2 have not been recorded in N46:N49.

    Also, not all the rows 46 - 49 will contain data, so not all need to be highlighted. The highlight is simply to show that, they have recorded data in the small table but not related it to data in the large table.

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,751

    Re: Conditional Formula - Multiple Criteria

    if S54 or S54 contain data AND OS1 or OS2 have not been recorded in N46:N49.

    Try using an * in the count
    AND( $A47 = "", $A46 > 0 OR( $S53 > 0, $S54 > 0), Countif($N46:$N49, "OS*")=0 )

    Now it will count OS, OS1, OS2 , OS with anything following

  9. #9
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Sefton
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    133

    Re: Conditional Formula - Multiple Criteria

    It's not only with the OS where a number would appear, numbers may appear on their own relating to another table.
    I think I've got it - may be able to make it smaller, but it seems to do what I want;
    =IF(AND($A47="",$A46>0,OR($S$53>0,$S$54>0),NOT(ISNUMBER(SEARCH("OS",$N46:$N49)))),TRUE,FALSE)

    Thanks for the help.
    Last edited by DHFE; 02-24-2014 at 09:09 AM.

  10. #10
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,751

    Re: Conditional Formula - Multiple Criteria

    thanks for the update, glad its all resolved now
    and thanks for the rep

+ 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 criteria conditional ranking and protecting multiple sheets in one go
    By samktlim in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-31-2013, 09:31 AM
  2. Replies: 8
    Last Post: 03-22-2013, 03:40 PM
  3. [SOLVED] Conditional sum with multiple criteria.
    By zicktechnology in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-15-2013, 11:21 PM
  4. [SOLVED] Formula for Multiple Criteria in Conditional Formatting
    By excelheaven in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-08-2013, 10:52 PM
  5. Replies: 5
    Last Post: 12-28-2012, 02:06 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