conditional formatting referencing 3 cells by row in a range

[TheMTCGal]

Hi,
I need to set up conditional formatting with 3 conditions and each condition
is to look at 3 different cells in a row and determine if they meet the
condition. Currently what I have setup does not limit the conditions to have
all 3 in the same row...it will indicate that I have met the condition when
in fact one of the values is only true if you look in different rows (see
example).
Here is what I have setup so far:
condition 1 -
AND((COUNTIF($E$19:$E$10000,$E19)>1),(COUNTIF($A$19:$A$10000,$A19)>1),(COUNTIF($F$19:$F$10000,$F19)>1))
condition 2 -
AND((COUNTIF($E$19:$E$10000,$E19)=1),(COUNTIF($A$19:$A$10000,$A19)>1),(COUNTIF($F$19:$F$10000,$F19)>1))
condtion 3 -
=AND((COUNTIF($E$19:$E$10000,$E29)>1),(COUNTIF($A$19:$A$10000,$A29)=1),(COUNTIF($F$19:$F$10000,$F29)=1))

and example of the data is as follows:
column a column b column c column d column e column f
row 18 "Unit " "Desc" "Type" "Status" "WO" "WO Desc"
row 19 1004M1 SWIVEL S 15 605161 REPLACE SEAL FOR COUPLING
row 20 1004M1 SWIVEL S 15 605161 REPLACE SEAL FOR COUPLING
row 21 1005M3 SL1 ROLL P 40 607696 Drive Service - Shutdown
row 22 1006 HEAD 1 S 15 582290 replace skirting
row 23 1006 HEAD 1 P 40 600453 FRMING HEAD INSPCTION
row 24 1006 HEAD 1 P 40 608227 FRMING HEAD INSPCTION
row 25 1006M12 ROLL 2 S 10 607840 Change rosta.
row 26 1007 CONV S 40 398889 INSTALL GREASE LINES ON SCREWS
row 27 1007 CONV P 40 605779 Swivel Trough Mechanical Insp
row 28 1008 BIN 4 S 40 441259 CHANGE DECK
row 29 1008M3 PICKER P 40 594158 Drive Service - Shutdown
row 30 1008M3 PICKER P 40 603352 Pick Rolls Insp - Operating
row 31 1008M3 PICKER P 40 605910 PICK ROLL DRIVE OPER-INSP

I set up the conditional formatting on column E. Cell E29 is meeting
condition 2 when it shouldn't because the 3 criteria cannot all be found in
Row 29...

Please help

 

[T. Valko]

The formula for condition 3:

=AND((COUNTIF($E$19:$E$10000,$E29)>1),(COUNTIF($A$19:$A$10000,$A29)=1),(COUNTIF($F$19:$F$10000,$F29)=1))

The COUNTIF criteria refers to E29, A29 and F29. The formulas for conditions
1 and 2 refer to E19, A19 and F19.

Is the formula for condition 3 correct?

Condition 2 for row 29 does meet the criteria.

AND((COUNTIF($E$19:$E$10000,$E19)=1),(COUNTIF($A$19:$A$10000,$A19)>1),(COUNTIF($F$19:$F$10000,$F19)>1))

Based on the sample data:

COUNTIF($E$19:$E$10000,$E19)=1 = TRUE (1 instance of 594158)

COUNTIF($A$19:$A$10000,$A19)>1 = TRUE (3 instances of 1008M3)

COUNTIF($F$19:$F$10000,$F29)>1 = TRUE (2 instances of Drive Service -
Shutdown)

 

[T. Valko]

Argh! Copy/paste typos...

Based on the sample data:

COUNTIF($E$19:$E$10000,$E29)=1 = TRUE (1 instance of 594158)

COUNTIF($A$19:$A$10000,$A29)>1 = TRUE (3 instances of 1008M3)

COUNTIF($F$19:$F$10000,$F29)>1 = TRUE (2 instances of Drive Service -
Shutdown)

 

[Ms-Exl-Learner]

Your 1st and 2nd condition is perfect. But the 3rd condition is incorrect
which is described below:-

condition 1
=AND((COUNTIF($A$19:$A$10000,$A19)>1),(COUNTIF($E$19:$E$10000,$E19)>1),(COUNTIF($F$19:$F$10000,$F19)>1))
condition 2 -
=AND((COUNTIF($E$19:$E$10000,$E19)=1),(COUNTIF($A$19:$A$10000,$A19)>1),(COUNTIF($F$19:$F$10000,$F19)>1))

But in your 3rd condition look the countif criteria you have mentioned the
Cell reference as $E29, $A29 & $F29 instead of $E19, $A19 & $F19. That is
the reason you are not getting the correct conditional formatting.

Your Wrong 3rd Conditional formatting Formula
condtion 3 -
=AND((COUNTIF($E$19:$E$10000,$E29)>1),(COUNTIF($A$19:$A$10000,$A29)=1),(COUNTIF($F$19:$F$10000,$F29)=1))

Actual formula should be like the below for Condition
=AND((COUNTIF($E$19:$E$10000,$E19)>1),(COUNTIF($A$19:$A$10000,$A19)=1),(COUNTIF($F$19:$F$10000,$F19)=1))

While applying the above three conditions Place the Cursor in A19 cell and
upto F19 by holding shift and pressing the right arrow and now extend your
selection upto row 10000 (i.e.) A19: F10000. Now go to Format>>Conditional
formatting and paste the first 2 conditions and the Corrected formula in 3rd
condition and apply the formatting accordingly.

Remember to Click Yes, if this post helps!