Minimum Ifs Function

J

Jon Ratzel

I need a formula that would work as a minimum ifs function similar to the
sumifs formula. Given the data set below I need the minimum list price for
the group in cell A1 but only only if the Use = Y and Finish = Red. Can
anyone help?

Group Use Finish List Price Min List Price
A X Red $10
B Y Blue $12
A Y Red $15 $15
C X Yellow $10
A Y Red $19 $15
B Y Red $9
 
P

Pete_UK

Are the words Group, Use, Finish and List_Price defined names? (eg
from row 3 onwards?) If so you can try this array* function:

=MIN(IF((Group=$A$1)*(Use="X")*(Finish="Red"),List_Price,10E10))

An aray function must be committed with the key combination CTRL-SHIFT-
ENTER (CSE) instead of the usual ENTER.

Hope this helps.

Pete
 
T

T. Valko

Try this array formula** :

=MIN(IF((Group="A")*(Use="Y")*(Finish="Red"),Price))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
S

Sandy Mann

With the suppied data in A1:D7 try:

=MIN(IF(($A$2:$A$7=A2)*($B$2:$B$7=B2)*($C$2:$C$7=C2),$D$2:$D$7))

which is an arroy formula which shpould be entered with Ctrl + Shift + Enter
not just Enter.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
J

Jon Ratzel

Hi Pete, thanks for helping.

I'm not sure if I understand your question. The words in the cells in the
Group, Use, and Finish columns are defined names that I would normally enter
as criteria in quotations as "Red" or "Y" if I were using a sumifs formula.
The List Price changes with each row of data and would be like the sum range
part of a sumifs formula.

When I tried your formula and the one from T. Valko of the next post I
didn't get the correct answer. I'm able to check everything by using a pivot
table and vlookups but it's a very manual process, so I'm hoping an array
formula would speed things up. Any other suggestions?
 
P

Pete_UK

Do the named ranges cover the same number of rows?

Did you commit the formula using CSE? (i.e. can you see curly braces
{ } around the formula when viewed in the formula bar?)

Was the answer you got anywhere near the correct answer?

Do you have any spaces at the end of any of the Red's or X's or Y's?

Pete
 
J

Jon Ratzel

Hi Pete,

The named ranges are covering the entire column. I'm also using regular
parethesis and not the {}'s. I'm getting $0 from the formula instead of
numbers ranging from $216 or $329. I don't have any spaces after any of the
finishes like Red or Blue either.

Thanks again for the help!
Jon
 
P

Pete_UK

You can't use full-column references in array formulae in Excel
versions before 2007, so you will need to make your named ranges
shorter (eg A2:A65536).

When you type in an array formula you need to use the key combination
of CTRL-SHIFT-ENTER instead of the usual ENTER. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - you do NOT type these yourself. If
you can't see any curly braces in the formula bar, however, it means
that you did not commit the formula as an array.

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top