Conditional highlighting of a cell or cells

[Haunted Ape]

Hello,

I am trying to find a way of making a cell become highlighted if any 2 or more cells within a selected range are greater than 0.

For example if A1 and A3 are 2 and 4 respectively but A2 is blank or 0 then A4 will become highlighted with the total.

Is this possible?

Thanks

 

[bigmyk2k]

There are a couple of ways of doing this, but the easiest is probably this two step process:
1) Add this function in A4: =sum(A1:A3)
2) Conditionally Format cell A4 to highlight with the formula: =(countif(A1:A3,">"&0))>1

 

[dpotta]

Add this formula to A4 and then apply conditional formatting to highlight the cell where A4 >0 ?

=IF(SUM(A1:A3)=0,"",SUM(A1:A3))

 

[Haunted Ape]

Thanks for the quick responses, I've tried and I can get that to work, however now that I am putting it into my spread sheet with a greater number of specified cells in the conditional format part I get the message:

"you've entered too many arguments for this function"

How do I correct the conditional format with the below specified cells please?

=(COUNTIF(Y16, Y29, Y42, Y55, Y68, Y83, Y96, Y109, Y122, Y135, Y148, Y161, Y174,">"&0))>15

Thanks

 

[Haunted Ape]

Thanks for the quick responses, I've tried and I can get that to work, however now that I am putting it into my spread sheet with a greater number of specified cells in the conditional format part I get the message:

"you've entered too many arguments for this function"

How do I correct the conditional format with the below specified cells please?

=(COUNTIF(Y16, Y29, Y42, Y55, Y68, Y83, Y96, Y109, Y122, Y135, Y148, Y161, Y174,">"&0))>15

Thanks

 

[dpotta]

Hi

If you look at the syntax of a COUNTIF you will begin to see why that formula will not work! =COUNTIF(Range,Criteria). You can only have one RANGE and one CRITERIA in a COUNTIF. If you took the first part of your formula =COUNTIF(Y16,Y29), Excel would count 1 if the value in the RANGE Y16 was equal to CRITERIA Y29.

Just to explain a bit: So type a RANGE into cells A2:A4 - In A2 type Apples, In A3 type Pears and in A4 type Bananas. The CRITERIA is saying Count me if I equal... So in B2 type, =COUNTIF(A2:A4,"Apples") This will return 1. 1 Value is Apples.