If a range of cells has more than one value

[Busymanjohn]

Hi, This should be easy, I have a range of data ,, say BO10:BO21, that contain results of an IF sumproduct, which returns a value of zero depending on the result, what I would like to do in another cell, say, BO6, is have a formula that returns a value of 1 if the range BO10:BO21 contains values greater than 1 in more than one cell ..... make sense?

 

[Montrey]

=if(sum(BO10:BO21)>=2,1,0)

 

[Busymanjohn]

Hi, not quite what I am looking for ,,,, to be a bit clearer, the range BO10:BO21 has a zero value except when the formula ( IF SUMPRODUCT ) returns a value, which can be far greater than 2. What I am looking to do is, in cell BO6, return a value of 1 when two or more cells in the range BO10:BO21 have a value greater than zero ,,,, so, if the range has only one cell with a value greater than zero I want to leave BO6 blank ( or zero ), only return the value 1 when two or more cells in that range have a value greater than 1 .. make sense?

 

[NARAYANK991]

Hi ,


Use the COUNTIF function , with the range as BO10:BO21 , and the criterion as ">0" ; this will return the number of cells in the range which have values greater than 0. Use this value in an IF statement e.g. =IF(COUNTIF(BO10:BO21,">0")>1,1,"").


Narayan

 

[nazmul_muneer]

@Busymanjohn

I think, NARAYANK's answer is perfect for your problem. Has your problem solved?

 

[Hui]

or

=1*(COUNTIF(BO10:BO21,">0")>1)