Min IFs

[jlkirst]

Probably best to start with the Maxif to show you where i am.

{=MAX(IF(Data.!A:A=SRLlist!A358,Data.!Z:Z))}. basically, there is list of 'names' (SRLlist!A358 in this case) that match names on another sheet (data! sheet, column A) for which there are multiple entries. This formula gives me the max (in this case a date, residing in Data.!, column 'Z'). this works fine.

But, i really need the MIN. But, it must be the min > 0. Many on the list of Data.! have no value for 'Z' and it comes as a zero.

This is what my little brain come up with, but returns the zero
{=MIN(IF(AND(Data.!A:A=SRLlist!A358, Data.!Z:Z>0), Data.!Z:Z))}

Probably not giving any real reference for the zero to be compared against.

 

[Aladin Akyurek]

Probably best to start with the Maxif to show you where i am.

{=MAX(IF(Data.!A:A=SRLlist!A358,Data.!Z:Z))}. basically, there is list of 'names' (SRLlist!A358 in this case) that match names on another sheet (data! sheet, column A) for which there are multiple entries. This formula gives me the max (in this case a date, residing in Data.!, column 'Z'). this works fine.

But, i really need the MIN. But, it must be the min > 0. Many on the list of Data.! have no value for 'Z' and it comes as a zero.

This is what my little brain come up with, but returns the zero
{=MIN(IF(AND(Data.!A:A=SRLlist!A358, Data.!Z:Z>0), Data.!Z:Z))}

Probably not giving any real reference for the zero to be compared against.

Try to use definite ranges like A2:A400 instead of A:A... That said:

Control+shift+enter, not just enter...

=MIN(
  IF(Data.!A2:A400=SRLlist!A358,
  IF(ISNUMBER(Data.!Z2:Z400),
  IF(Data.!Z2:Z400>0,
    Data.!Z2:Z400))))

 

[jlkirst]

Thanks! Worked great. I knew I was probably making things harder without definite ranges. Hadn't considered using ISNUMBER. Very slick.