Excel formula mutiple condition on more one row on conditional formatting

Question

I want apply the conditional formatting on Column "A", "B", "C" and "D". I use this formula: =NB.SI.ENS($A:$A;A1;$E:$E; "ERROR") > 0 in french or this formula for English: =COUNTIFS($A:$A, A1, $E:$E, "ERROR") > 0

I add in configuration: apply to =$A:$D

I want apply the conditional formatting on Column "A", "B", "C" and "D" but the result is only on Column "A".

I have 2 questions:

• Think you have to change the formula or change the configuration of the conditional formatting?

• If I want change ="ERROR" by contain "ERROR: ..." is possible?

MY EXCEL FILE IS HERE

Answer 1

Salut !

Replace the formula with :

=COUNTIFS($A:$A,$A1,$E:$E,"ERROR*")>0

In French:

=NB.SI.ENS($A:$A;$A1;$E:$E;"ERROR*")>0

2nd argument = $A1, not A1. "ERROR*" counts all cells beginning with "ERROR".