conditioned check function

[_Luca_]

Hi everybody! I have 2 columns the first one with text (allowed values are null, "a", "r") and the 2nd one are integer. I need to check if the integers in the 2nd column are bigger (>) then a given number, but the given number is different if the integer refers to an "a" value or a "r" value. Something like this:

IF ((the value in the rows of the second column where the value on the same row in the 1st column is "a" are >4) OR (the value in the rows of the second column where the value on the same row in the 1st column is "r" are >2)) THEN do this ELSE do that

Please Login or Register  to view this content.

In another cell I'd like to check if numbers in the rows of columnB are bigger than 4 (if in the same row under columnA there is an "a") or bigger than 2 (if in the same row under columnA there is a "r"), and notify if there is at least one that is bigger.

to better explain i'll probably do like this in php (for those who know it...):

$column1=array("r","a","r","","a","a","","r","a");
$column2=array(2,3,3,0,4,6,0,1,4);

foreach($column2 as $key => $value){
   if ($column1[$key]='a'){
      if ($value>4){ print("Error"); }
   }else{
      if ($value>2){ print("Error"); }
   }
}  

Many tnx for reply! =)

[daddylonglegs]

Hello Luca,

If you want a formula that checks that condition for all rows try

=IF(SUMPRODUCT((A1:A9="a")*(B1:B9>4)+(A1:A9="r")*(B1:B9>2)),"notify","OK")

[_Luca_]

Cool tnx!works perfectly!
can you please explain that anyway?I can't understand what happens in that formula...and what does A1:A9="r" or B1:b9>4 means?I've never seen such a syntax...