Change Cell Color if more than one has a value, or if another has no value.

[UncleBajubjubs]

Good morning, I have a table with 4 columns where columns A, B, and C can have an amount of money, and D a text string. It's intended that the the user will put in an amount of money into one of A, B, or C, and they will enter a value into column D for any rows that have anything in the previous columns.

In this table as an example, rows 1 and 2 are filled out correctly, but row 3 is wrong because both A and C have a value, and row 4 is wrong because there is a value in B, but nothing in D.

56			E1
	22		G6
71		34	B2
	65

How might I set it so that so cells A, B, and C will be highlighted if more than 1 of the 3 have values, and D will be highlighted if there is no label when 1 of A,B, or C have a value?
Thanks

 

[Saurabhj]

Hi,

Use conditional formatting:
For A1 to C4

=COUNTA($A2:$C2)>1

For D1 to D4

=Isblank($D2)

 

[UncleBajubjubs]

Thanks, that works pretty great!

Is there way to make it not count cells that have "0" in them? If I have 2 and 0 in A and B, that makes it format.

 

[Saurabhj]

Thanks for the feedback.

Are you saying that if A, B, or C contains 0 this should also be considered as blank.

 

[UncleBajubjubs]

Correct, if a row has 0, 2, and 0, that should be considered correctly filled out, and thus not format.
Thanks

 

[Saurabhj]

Hi,

Check below:

=AND(COUNTA($A2:$C2)>1,SUM($A2:$C2)>MAX($A2:$C2))