Excel Formula

Question

I need a formula that returns a count of 1 if the cells in range BG8:BH8 are less than 50 and a count of 0 if they are equal to 50.  I have this formula =IF(COUNTIF(AA8:AO8,"0") >0,1,"0") that I tried to adapt but could not get it to work.  What does the "0" after the AO8 refer to.  I tried to rewrite the formula =IF(COUNTIF(AA8:AO8,"0") <50,1,"0") but it did not work.  It returned a value of 1 whether the cells were <50 or not

Answer 1

And below formula returns 0 if BOTH BF8 & BG8 are equal to 50, returns 1 if BOTH BF8 & BG8 are less than 50, else returns a blank.

=IF(AND(BF8=50,BG8=50),0,IF(AND(BF8<50,BG8<50),1,""))

Regards,

Amit Tandon

Answer 2

Refer below formula:

If BOTH BF8 & BG8 are less than 50 the formula returns 1, else it returns 0.

=IF(AND(BF8<50,BG8<50),1,0)

Below formula returns 50 only if BOTH cells are equal to 50, else it returns 1

=IF(AND(BF8=50,BG8=50),0,1)

In case this is what you need.

Regards,

Amit Tandon

Answer 3

The number of cells in your range AA8:AO8 are only 15 hence they will not count 50. The "0" refers to count of cells in the range AA8:AO8 which contain "0". In case you wish to count cells containing 0 (zero), you may use below formula(s):

This returns 1 if the number of cells containing zero (in range BG1:BH100) are less than 50 & in case of 50 or more it returns 0.

=IF(COUNTIF(BG1:BH100,0)<50,1,0)

Returns 1 for less than 50, returns 0 for exact 50 & returns blank for more than 50.

=IF(COUNTIF(BG1:BH100,0)<50,1,IF(COUNTIF(BG1:BH100,0)=50,0,""))

Regards,

Amit Tandon

Answer 4

hi there.  your question is pretty confusing.  you mentioned BG8:BH8 but actually showed a formula eg of AA8:AO8.  so let me just use the latter.  there are 15 cells in AA8:AO8.  so do you mean that if all of them are values less than 50, you want to return 1?  you could use:

=--(COUNTIF(AA1:AO1,"<50")=15)

or 

=--(COUNTIF(AA1:AO1,"<50")=COLUMNS(AA1:AO1))

to show 1 if just any of them is less than 50:

=--(COUNTIF(AA1:AO1,"<50")>0)

if you want to count the number of zeroes like Amit has interpreted, these are the two alternative formulas from his:

=--(COUNTIF(BG1:BH100,0)<50)

=IF(COUNTIF(BG1:BH100,0)>50,"",--(COUNTIF(BG1:BH100,0)<50))

Answer 5

Thanks so much for this.  Unfortunately I did not explain the situation clearly.  The formula I referenced was an example of a formula that worked elsewhere in the workbook but given your explanation I am not sure why it works.  The actual cell range I am working with is BG8:BH8. Both BG8 and BH8 have drop down values of 50, 40, 30, 20, 10, and  zero.  The formula I am trying to develop states that if BG8:BH8 are less than 50 then it returns a value of one.  But if they are 50 then the value is 0