Need help with MIN IF AND

[tinfanide]

From A1 to A6:


4,0,5,1,2,3

=MIN(IF($A$1:$A$6<>0,IF($A$1:$A$6<>1,$A$1:$A$6)))

This formula, with the use of nested IF, works. (Return: 2)

=MIN(IF(AND($A$1:$A$6<>0,$A$1:$A$6<>1),$A$1:$A$6))

This formula, with the use of AND inside IF, does not work. (Return: 0)

I'd like to ask how to make the second formula (use of AND) works.

Many thanks!

[Pecoflyer]

Did you commit this array formula with Ctrl-Shift-Enter?

[macropod]

Simpler still:
=MIN(IF(($A$1:$A$6<>0)*($A$1:$A$6<>1),$A$1:$A$6))
or, if you can only have integers of 0 or more:
=MIN(IF($A$1:$A$6>1,$A$1:$A$6))
either as an array formula.

[Pecoflyer]

A non array solution

=SMALL(A1:a6,COUNTIF(A1:a6,0)+COUNTIF(A1:a6,1)+1)

EDIT corrected typo

[tinfanide]

Originally Posted by Pecoflyer

Did you commit this array formula with Ctrl-Shift-Enter?

Yes. Both of them.

[tinfanide]

Originally Posted by Pecoflyer

A non array solution

=SMALL(A1:a6,COUNTIF(A1:a6,0)+COUNTIF(A1:a6,1)+1)

EDIT corrected typo

Thanks. But if the array is like this:

From A1 to A6:
4,0,5,1,2,1

Then

COUNTIF($A$1:$A$6,1)
' will return 2

[tinfanide]

Originally Posted by macropod

Simpler still:
=MIN(IF(($A$1:$A$6<>0)*($A$1:$A$6<>1),$A$1:$A$6))
or, if you can only have integers of 0 or more:
=MIN(IF($A$1:$A$6>1,$A$1:$A$6))
either as an array formula.

Yes, with the use of asterisk, it works.
But could I ask why mine with the use of the "AND" operator doesn't work?

[Pecoflyer]

Originally Posted by tinfanide

Thanks. But if the array is like this:

From A1 to A6:
4,0,5,1,2,1

Then

COUNTIF($A$1:$A$6,1)
' will return 2

Yes, and what is wrong about it? Aren't you looking for the smallest value different from 0 or 1 ?

[macropod]

Originally Posted by tinfanide

could I ask why mine with the use of the "AND" operator doesn't work?

I don't really understand why the AND operator doesn't work in this case, since what it's doing seems to me to be logically the same as the formula I posted.

[tinfanide]

Originally Posted by Pecoflyer

Yes, and what is wrong about it? Aren't you looking for the smallest value different from 0 or 1 ?

Nope. I've confused myself. You're right.

[beginner]

Originally Posted by tinfanide

This formula, with the use of AND inside IF, does not work. (Return: 0)
...
But could I ask why mine with the use of the "AND" operator doesn't work?

I hope that you can help explanation of the picture below
Function AND have FALSE result, You can try Evaluate formula

[Kevin@Radstock]

AGGREGATE Function:

=AGGREGATE(15,6,A1:F1/((A1:F1<>0)*(A1:F1<>1)),1)