Thread: Find minimum with criteria - "MINIF"

Hello,

I have following problem I have some table - 2 columns - in first is text in second is value.

example:
A 10
B 5
C 3
A 15
C 8
D 7
A 9

I need to use formula, which shows me minimal value for choosen text.
I tried matrix formula like {=min(if(text table=choosen text; second column;0}, but it's not working

Any idea?

Thanks in advance

Zeman Martin

Hi,

With an array ...(control+shift+enter)

=MIN(IF(A1:A7=”A”,B1:B7))

HTH

JR, that won't work I'm afraid (unless all values in A1:A7 were A)

Code:

=MIN(IF(A1:A7="A",B1:B7))
CSE

Still got some typos in your original which might be worth editing to avoid confusion.

For sake of OP, the key in the MIN array is to ensure that the Array of values used contains numbers only where A1:A7 matches required value (else populate with Boolean) otherwise you're likely to get 0 return more often than not

To illustrate using your sample data:

Code:

=MIN((A1:A7="a")*(B1:B7))

will in essence evaluate to

Code:

=MIN({10;0;0;15;0;0;9})
(ie TRUE/FALSE * number -> where FALSE this will result in 0)

and the MIN of that array is obviously 0

whereas

Code:

=MIN(IF(A1:A7="a",B1:B7))

will evaluate to

Code:

=MIN({10;FALSE;FALSE;15;FALSE;FALSE;9})

and the Booleans in this context (inline array) will be ignored - thus result is 9