Conditional formatting with Multiple Criteria

I'm trying to conditionally format a total row to turn or not turn a color based on the individual rows that make up that sum. I've attached a sample to go along with my explanation.

My sheet is broke into 4 units with a total at the top. Each unit has a color that helps identify it. I would like for the total line to turn the color of the unit that makes up it's total only if that is the only unit part of the total otherwise I would like for it to stay non colored indicating that there are multiple units involved. It seems fairly easy to me how to get the total row to turn a color if a units row is greater than 0, but my mind block comes when I think of combining those with some kind of conditional formatting formula/step that leaves the cell non colored if there is no total or the total is made up of more than one unit.

Conditional formatting example.xlsx

You need 4 rules
With range B1:H1 selected
Rule #1: =AND(COUNT(B$2:B$5)=1,B$2<>"") format orange
Rule #2: =AND(COUNT(B$2:B$5)=1,B$3<>"") format green
and you can do #3 and #4 from that

The color in rows 2 through 5 is going to be the same color as it is in the same row of column A. It was really more for display purposes. The formatting I'm concerned about is for the total row which is 1. Say if row 2 is the only number in that column then I want the total to turn orange. If there is a number in 3 and 4 then I want it to stay non colored.

Originally Posted by dilipandey

HI Kgibson,

Let me know if I am getting it correctly..
Row 2 is orange in color (except blank cells) because it has only 100 in that row
and same goes with column B as it has only 100 ?

Regards,
DILIPandey

<click on below 'star' if this helps>

Looks like that did it. Thanks for the help.

Originally Posted by Cutter

You need 4 rules
With range B1:H1 selected
Rule #1: =AND(COUNT(B$2:B$5)=1,B$2<>"") format orange
Rule #2: =AND(COUNT(B$2:B$5)=1,B$3<>"") format green
and you can do #3 and #4 from that

You're welcome. Don't forget to mark your thread as SOLVED.