How to determine if a row of numbers (12 cells) are all less than or equal to 600

[Jaimee001]

Hi All,
I have a report with 500 rows and 12 columns (2013 by month). For each row there is a number in each cell. I need to determine what rows have a full year where each month the number is less than or equal to 600. I thought I could do it by formatting the cell color but then I would need to do some VB and I'm not really good at VB. I tried an If statement:
if(B2:M2 <601),"Y","N") but that doesn't work quite right.
I've attached a report (with the yellow highlights) Row 2 and 4 are what I'm looking for. For each of those rows, each month is <=600. Hopefully, I'm not being too confusing.
Thank you for all that you do!

Regards,
Jaimee

 

[kchiba]

Hi Jaimee,

I have put the formula in the attached file.

In N2 you have
=IF(COUNTIF(C2:M2,">600"),TRUE,FALSE)

 

[Oxidised]

Hi Kchiba, I think the formula should be modified to read
=IF(COUNTIF(B2:M2,">600"),FALSE,TRUE) to give the result as requested

I find it makes more sense to me to be consistent with the requirement and write the formula this way:
=IF(COUNTIF(B2:M2,"<=600")=12,TRUE,FALSE)

Or you could do an array entered formula (shift+ctrl+enter)
{=IF(MAX(--(IF(B2:M2 >600,B2:M2)))>0,FALSE,TRUE)} or even {=MAX(--(IF(B2:M2 >600,B2:M2)))=0}

Or a sumproduct formula (love sumproduct!!)
=SUMPRODUCT((B2:M2)*(B2:M2>600))=0

Lots of options, hope you find one that works.

 

[NARAYANK991]

Hi ,

I am not sure I have understood your question , but won't a simple =MAX($B2:$M2)<=600 do ?

Can you see your file ?

Narayan

 

[kchiba]

Hi,

Have changed the formula, please check

=IF(COUNTIF(C2:M2,">600"),FALSE,TRUE)


kanti

 

[waqaracao]

Pl try this formula =IF(COUNTIF(B2:M2,"<=600")=12,"Y","N") (file attached)

 

[Jaimee001]

THANK YOU THANK YOU THANK YOU ALL!!!!!
I totally forgot about the COUNTIF formula...
Can't thank you enough

Jaimee