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

On May 6, 5:13*pm, Jon Ratzel
wrote:
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

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)

--
Biff
Microsoft Excel MVP


"Jon Ratzel" wrote in message
...
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

With the suppied data in A1:D7 try:

=MIN(IF(($A$2:$A$7=A2)*($B$2:$B$7=B2)*($C$2:$C$7=C 2),$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


Replace @mailinator.com with @tiscali.co.uk


"Jon Ratzel" wrote in message
...
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

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?



"Pete_UK" wrote:

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

On May 6, 5:13 pm, Jon Ratzel
wrote:
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

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

On May 6, 6:32*pm, Jon Ratzel
wrote:
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?



"Pete_UK" wrote:
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

On May 6, 5:13 pm, Jon Ratzel
wrote:
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- Hide quoted text -

- Show quoted text -

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


"Pete_UK" wrote:

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

On May 6, 6:32 pm, Jon Ratzel
wrote:
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?



"Pete_UK" wrote:
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

On May 6, 5:13 pm, Jon Ratzel
wrote:
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- Hide quoted text -

- Show quoted text -

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

On May 8, 5:43*pm, Jon Ratzel
wrote:
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



"Pete_UK" wrote:
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

On May 6, 6:32 pm, Jon Ratzel
wrote:
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?

"Pete_UK" wrote:
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

On May 6, 5:13 pm, Jon Ratzel
wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -