Min If???

R

rmeister

I am trying to return the Min of 1 column if the criteria I specif
matches.

For Example, I want this to work like a SUMIF. If A:A is equal to CEL
C2 then return the minimum of column B:B.


Is this Possible??
 
B

Bart Snel

rmeister schreef:
I am trying to return the Min of 1 column if the criteria I specify
matches.

For Example, I want this to work like a SUMIF. If A:A is equal to CELL
C2 then return the minimum of column B:B.


Is this Possible???
Click to expand...
You almost gave the answer yourself: =IF(SUM(A:A)=C2;MIN(B:B);value if
not true)
 
R

rmeister

I should clear this up.

I am not actually trying to do a Sum of anything. I was just making
reference to that particular function.

In a perfect world the function wold be MINIF. That would operate the
same as the SUMIF However rather than getting back the sum I would get
back the Minimum.

Hopefully this is more clear. The more I think the more unclear I
become so any help would be greatly appreciated.
 
A

Aladin Akyurek

With E2 housing the criterion of interest:

=MIN(IF(A2:A100=E2,B2:B100))

which must be confirmed with control+shift+enter, not just with enter.
 
P

Pete

You can make use of MIN and IF in an array formula. For example, in
this formula:

=MIN(IF(((Bill_no =2)*(Acc_no="abc")),call_date,100000))

I want to find the earliest date in the named range "call_date" which
has an invoice number of 2 and an account number of abc, where
"Bill_no" and "acc_no" are also named ranges. A similar formula can be
used with MAX to find the latest date that meets these criteria, as
follows:

=MAX(IF(((Bill_no =2)*(Acc_no="abc")),call_date,0))

As these are array formulae, you have to use CTRL-SHIFT-ENTER instead
of just <enter> to get them to work - this key combination also results
in putting curly brackets around the formula. Other criteria can be
added by *(criteria) in the first part of the formula for an AND
condition, or +(criteria) for an OR condition.

Hope this helps.

Pete
 
Top