Conditional formatting based on multiple columns

sobeitjedi

Board Regular
Joined
Mar 13, 2006
Messages
198
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.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Yes</td><td style=";">No</td><td style=";">Yes</td><td style=";">No</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td><td style=";">Yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Yes</td><td style=";">No</td><td style=";">No</td><td style=";">No</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">No</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Yes</td><td style=";">No</td><td style=";">No</td><td style=";">No</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Yes</td><td style=";">No</td><td style=";">No</td><td style=";">No</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td><td style=";">No</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">No</td><td style=";">Yes</td><td style=";">No</td><td style=";">No</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">No</td><td style=";">No</td><td style=";">No</td><td style=";">No</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Yes</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">No of Yes</td><td style="text-align: right;;">48</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">No of No</td><td style="text-align: right;;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B22</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$1:$D$20,"Yes"</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B23</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$1:$D$20,"No"</font>)</td></tr></tbody></table></td></tr></table><br />
 

sobeitjedi

Board Regular
Joined
Mar 13, 2006
Messages
198
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

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Yes</td><td style=";">No</td><td style=";">Yes</td><td style=";">No</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td><td style=";">Yes</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Yes</td><td style=";">No</td><td style=";">No</td><td style=";">No</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">No</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Yes</td><td style=";">No</td><td style=";">No</td><td style=";">No</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Yes</td><td style=";">No</td><td style=";">No</td><td style=";">No</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td><td style=";">No</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">No</td><td style=";">Yes</td><td style=";">No</td><td style=";">No</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">No</td><td style=";">No</td><td style=";">No</td><td style=";">No</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Yes</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">Yes</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">No</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E1</th><td style="text-align:left">=COUNTIF(<font color="Blue">A1:D1,"Yes"</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B22</th><td style="text-align:left">=COUNTIF(<font color="Blue">$E$1:$E$20,4</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B23</th><td style="text-align:left">=COUNTIF(<font color="Blue">$E$1:$E$20,0</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

sobeitjedi

Board Regular
Joined
Mar 13, 2006
Messages
198
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

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Yes</td><td style=";">No</td><td style=";">Yes</td><td style=";">No</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td><td style=";">Yes</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Yes</td><td style=";">No</td><td style=";">No</td><td style=";">No</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">No</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Yes</td><td style=";">No</td><td style=";">No</td><td style=";">No</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style=";">No</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">No</td><td style=";">Yes</td><td style=";">No</td><td style=";">No</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;"></td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">No</td><td style=";">No</td><td style=";">No</td><td style=";">No</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Yes</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">Yes</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">No</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E1</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">MATCH(<font color="Green">"No",A1:D1,0</font>)</font>),0,IF(<font color="Red">COUNTIF(<font color="Green">A1:D1,"Yes"</font>)=4,1,""</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B22</th><td style="text-align:left">=COUNTIF(<font color="Blue">$E$1:$E$20,1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B23</th><td style="text-align:left">=COUNTIF(<font color="Blue">$E$1:$E$20,0</font>)</td></tr></tbody></table></td></tr></table><br />
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
you're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,102,542
Messages
5,487,469
Members
407,602
Latest member
clang663

This Week's Hot Topics

Top