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

1. ## 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

2. ## Re: Conditional formatting based on multiple columns

put this CF in A1 and apply to all rows

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. ## 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. ## Re: Conditional formatting based on multiple columns

Originally Posted by sobeitjedi
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)

5. ## 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. ## Re: Conditional formatting based on multiple columns

Originally Posted by sobeitjedi
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

Originally Posted by sobeitjedi

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. ## Re: Conditional formatting based on multiple columns

Many thanks - think I've cracked it now!

8. ## Re: Conditional formatting based on multiple columns

you're welcome