Array MIN that ignores zeros

[Silverjman]

{=MIN(($A122=$A$2:$A$2841)*$S$2:$S$2841)}
Column A is names S Dates so the result of this formula is Jan1901 or 0. COUNTIFS don't work in array formulas, correct? So I can't use SMALL(XXXXXXXX,COUNTIFS(XXX,0)+1)

I'm trying to find the first date a name appears...Mucho thanks!

 

[gsistek]

Try using
{=MIN(IF($A$2:$A$2841=$A122,$S$2:$S$2841,9E+307))}

*Array entered formulas are entered using CTRL-SHIFT-ENTER instead of just ENTER. If done correctly, Excel will insert the curly braces around your formula.

 

[barry houdini]

Try it like this:

=MIN(IF($A122=$A$2:$A$2841,$S$2:$S$2841))

 

[Mike LH]

Hi,

Try this, it's an ARRAY so ctrl+shift+enter


=MIN(IF(A2:A2841=A122,IF(S2:S2841>0,S2:S2841)))

 

[Silverjman]

AHHHHHH THANKS Mike, I danced around that for an embarrassingly long time but was not going to get it, now I can sleep tonight!

Gsistek I am very confused by the "9E+307" I tried hard to get it to work for the range but alas

Barry, I had been trying that earlier to no avail

Thanks ALL, your generosity is/was much appreciated!!!!!!!!!!!!!!!