Minimum Ifs Function

  • Thread starter Thread starter Jon Ratzel
  • Start date Start date
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
 
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
 
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)
 
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
 
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?
 
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
 
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
 
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
 
Back
Top