Conditional formatting - fill range if all cells in range are not blank

[Kathi_TheTrueOne]

Hi,

I have a table with 3 evaluation scores per agent. It looks like this (see attached):
A1 - name of the agent
A2 - score 1; A3 - score 2; A4 - score 3

B1 - name of the agent
B2 - score 1; etc.

I would like to set a conditional formatting to highlight A2:A4 if all 3 cells in this range have a score (a.k.a. are not blank). If one or more of these cells is empty (no score yet), the range will not highlight.

I tried different formulas that I found online, but they didn't quite work:
=COUNTA(A2:A4)=3
=COUNTBLANK(A2:A4)=0
=SUMPRODUCT(--(A2:A4<>""))=3
- all 3 only filled A2 but not A3 and A4, although the cond. formatting should apply for =$A$2:$A$4

Apparently I am doing something wrong, but I just can't figure out what

Do you perhaps have any idea what I'm doing wrong?

Thanks a lot!

BTW Hello, I´m new here on this forum, so hope I don't make too many mistakes. If so, please do let me know. Thanks!

Kathi

[mehmetcik]

=a2&a3&a4 <> ""

=CONCAT(A2:A4) <> ""

[Kathi_TheTrueOne]

Hi mehmetcik,

thanks for the formulas, but somehow these trigger the fill even when all 3 cells are blank...

However your formulas brought me to an idea that I found to be working....quite a long workaround I guess, but I take it for now :-D

I got this working:
=IF($A$2<>"","filled","nope")&(IF($A$3<>"","filled","nope"))&IF($A$4<>"","filled","nope")="filledfilledfilled"

This however only works like intended when the references are absolute. When I change it to relative references or only "partial" absolute references ("$A2", "A$2", "A2") it only fills the first cell like the formulas I tried at the very beginning.

I tried your formulas too with absolute references, but that ended up in the fill of all 3 cells alreadybeing triggered if only 1 of them was not blank.

As I wrote, not exactly smooth and clear, but for now it works :-D

[JeteMc]

Using the file attached to post #1, I applied the following rule to B2:H4

Please Login or Register  to view this content.

D2:D4 and G2:G4 highlighted.
Let us know if you have any questions.