Conditional formatting based on multiple columns
Results 1 to 8 of 8

Thread: Conditional formatting based on multiple columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2006
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Conditional formatting based on multiple columns

    Hi

    In my sheet, columns a, b, c and d can contain either "yes" or "no" - the rest of the columns contain text

    I want to colour the row red if any of abcd contains a no
    I want to colour the row green only if all of abcd contain yes

    I then want to count how many rows are coloured red
    I then want to count how many rows are coloured green

    Sounds simple, but I'm struggling - please help.

  2. #2
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Conditional formatting based on multiple columns

    put this CF in A1 and apply to all rows

    Untitledbc6642699190a6c9.jpg

    and the countif() to count them

    ABCD
    1YesNoYesNo
    2YesYesNoYes
    3YesYesYesNo
    4YesYesYesYes
    5YesNoNoNo
    6YesYesYesNo
    7NoNoYesYes
    8YesYesYesYes
    9YesNoNoNo
    10YesNoNoNo
    11YesYesYesYes
    12YesYesNoNo
    13YesYesYesNo
    14NoYesNoNo
    15NoYesYesYes
    16NoYesYesNo
    17NoYesYesYes
    18NoYesYesYes
    19NoNoNoNo
    20YesNoYesYes
    21
    22No of Yes48
    23No of No32

    Sheet1



    Worksheet Formulas
    CellFormula
    B22=COUNTIF($A$1:$D$20,"Yes")
    B23=COUNTIF($A$1:$D$20,"No")


  3. #3
    Board Regular
    Join Date
    Mar 2006
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional formatting based on multiple columns

    Not quite ....

    The conditional format for yes/green works, thank you
    Using the same principle to colour red for no, only colours the rows that all abcd are no, not where a single no occurs

    Also, the count formula is counting all cells where a yes or no occurs - I want the rows where either all abcd are a yes, or where abcd contains a no.

  4. #4
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Conditional formatting based on multiple columns

    Quote Originally Posted by sobeitjedi View Post
    Not quite ....

    The conditional format for yes/green works, thank you
    Using the same principle to colour red for no, only colours the rows that all abcd are no, not where a single no occurs

    Also, the count formula is counting all cells where a yes or no occurs - I want the rows where either all abcd are a yes, or where abcd contains a no.
    to highlight the No rows just change yes to no to the second CF formula, as for the No of 4 yes etc, it's easier with a helper column like this

    ABCDE
    1YesNoYesNo2
    2YesYesNoYes3
    3YesYesYesNo3
    4YesYesYesYes4
    5YesNoNoNo1
    6YesYesYesNo3
    7NoNoYesYes2
    8YesYesYesYes4
    9YesNoNoNo1
    10YesNoNoNo1
    11YesYesYesYes4
    12YesYesNoNo2
    13YesYesYesNo3
    14NoYesNoNo1
    15NoYesYesYes3
    16NoYesYesNo2
    17NoYesYesYes3
    18NoYesYesYes3
    19NoNoNoNo0
    20YesNoYesYes3
    21
    22Yes3
    23No1

    Sheet1



    Worksheet Formulas
    CellFormula
    E1=COUNTIF(A1:D1,"Yes")
    B22=COUNTIF($E$1:$E$20,4)
    B23=COUNTIF($E$1:$E$20,0)

    Last edited by AlanY; Jul 16th, 2019 at 05:22 AM.

  5. #5
    Board Regular
    Join Date
    Mar 2006
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional formatting based on multiple columns

    Sorry, but you still don't understand - perhaps I'm not explaining well enough?

    If I change the yes to a no in the second CF formula, using your table, it would only colour row 19. I want to colour the rows where a no occurs in any of abcd.

    As for the count, in your table the yes is correct as there are 3 rows with all yes's. But the no count should equal 17 (rows where abcd contain a no) - would prefer to do this without a helper column, if possible?

  6. #6
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Conditional formatting based on multiple columns

    Quote Originally Posted by sobeitjedi View Post
    Sorry, but you still don't understand - perhaps I'm not explaining well enough?

    If I change the yes to a no in the second CF formula, using your table, it would only colour row 19. I want to colour the rows where a no occurs in any of abcd.
    ok, change the formula to =COUNTIF($A1:$D1,"No")>0

    Quote Originally Posted by sobeitjedi View Post

    As for the count, in your table the yes is correct as there are 3 rows with all yes's. But the no count should equal 17 (rows where abcd contain a no) - would prefer to do this without a helper column, if possible?
    a similar approach

    ABCDE
    1YesNoYesNo0
    2YesYesNoYes0
    3YesYesYesNo0
    4YesYesYesYes1
    5YesNoNoNo0
    6YesYesYesNo0
    7NoNoYesYes0
    8YesYesYesYes1
    9Yes
    10YesNoNoNo0
    11YesYesYesYes1
    12YesYesNo0
    13YesYesYesNo0
    14NoYesNoNo0
    15NoYesYesYes0
    16NoYesYesNo0
    17YesYesYes
    18NoYesYesYes0
    19NoNoNoNo0
    20YesNoYesYes0
    21
    22Yes3
    23No15

    Sheet1



    Worksheet Formulas
    CellFormula
    E1=IF(ISNUMBER(MATCH("No",A1:D1,0)),0,IF(COUNTIF(A1:D1,"Yes")=4,1,""))
    B22=COUNTIF($E$1:$E$20,1)
    B23=COUNTIF($E$1:$E$20,0)


  7. #7
    Board Regular
    Join Date
    Mar 2006
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional formatting based on multiple columns

    Many thanks - think I've cracked it now!

  8. #8
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Conditional formatting based on multiple columns

    you're welcome

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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