Conditional formatting based on multiple columns

[sobeitjedi]

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.

 

[AlanY]

put this CF in A1 and apply to all rows

<img src="https://www.pixelsbin.com/images/2019/07/16/Untitledbc6642699190a6c9.jpg" alt="Untitledbc6642699190a6c9.jpg" border="0">

and the countif() to count them

Book1
	A	B	C	D
1	Yes	No	Yes	No
2	Yes	Yes	No	Yes
3	Yes	Yes	Yes	No
4	Yes	Yes	Yes	Yes
5	Yes	No	No	No
6	Yes	Yes	Yes	No
7	No	No	Yes	Yes
8	Yes	Yes	Yes	Yes
9	Yes	No	No	No
10	Yes	No	No	No
11	Yes	Yes	Yes	Yes
12	Yes	Yes	No	No
13	Yes	Yes	Yes	No
14	No	Yes	No	No
15	No	Yes	Yes	Yes
16	No	Yes	Yes	No
17	No	Yes	Yes	Yes
18	No	Yes	Yes	Yes
19	No	No	No	No
20	Yes	No	Yes	Yes
21
22	No of Yes	48
23	No of No	32
Sheet1

Cell Formulas
Range	Formula
B22	=COUNTIF($A$1:$D$20,"Yes")
B23	=COUNTIF($A$1:$D$20,"No")

 

[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.

 

[AlanY]

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

Book1
	A	B	C	D	E
1	Yes	No	Yes	No	2
2	Yes	Yes	No	Yes	3
3	Yes	Yes	Yes	No	3
4	Yes	Yes	Yes	Yes	4
5	Yes	No	No	No	1
6	Yes	Yes	Yes	No	3
7	No	No	Yes	Yes	2
8	Yes	Yes	Yes	Yes	4
9	Yes	No	No	No	1
10	Yes	No	No	No	1
11	Yes	Yes	Yes	Yes	4
12	Yes	Yes	No	No	2
13	Yes	Yes	Yes	No	3
14	No	Yes	No	No	1
15	No	Yes	Yes	Yes	3
16	No	Yes	Yes	No	2
17	No	Yes	Yes	Yes	3
18	No	Yes	Yes	Yes	3
19	No	No	No	No	0
20	Yes	No	Yes	Yes	3
21
22	Yes	3
23	No	1
Sheet1

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

 

[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.

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?

 

[AlanY]

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

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

Book1
	A	B	C	D	E
1	Yes	No	Yes	No	0
2	Yes	Yes	No	Yes	0
3	Yes	Yes	Yes	No	0
4	Yes	Yes	Yes	Yes	1
5	Yes	No	No	No	0
6	Yes	Yes	Yes	No	0
7	No	No	Yes	Yes	0
8	Yes	Yes	Yes	Yes	1
9	Yes
10	Yes	No	No	No	0
11	Yes	Yes	Yes	Yes	1
12	Yes	Yes		No	0
13	Yes	Yes	Yes	No	0
14	No	Yes	No	No	0
15	No	Yes	Yes	Yes	0
16	No	Yes	Yes	No	0
17		Yes	Yes	Yes
18	No	Yes	Yes	Yes	0
19	No	No	No	No	0
20	Yes	No	Yes	Yes	0
21
22	Yes	3
23	No	15
Sheet1

Cell Formulas
Range	Formula
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)

 

[sobeitjedi]

Many thanks - think I've cracked it now!

 

[AlanY]

you're welcome