Min-Max question

[Gina]

Worksheet named "Data" has two columns, column A is types of fruit. Column B
is quantity of that fruit.

Apples 1
Apples 5
Apples 15
Oranges 1
Oranges 4
Oranges 12


How do I, from another worksheet in the workbook, make a formula that
says--- Look in Data worksheet, and return the minimum quantity of apples.

I have tried several combinations of the "If" and "Min" functions, but can
not seem to put them together well. Can anyone help?

Gina

 

[Gary''s Student]

=MIN(IF(Data!A1:A6="Apples",Data!B1:B6))

MUST be an array formula entered with CNTRL-SHFT-ENTER rather than just the
ENTER key.

 

[Gina]

Thank you, this worked great. I was missing the control-shift-enter, and
your reminder made it work well.

Gina

 

[Gina]

=MIN(IF(Data!A1:A6="Apples",Data!B1:B6))

If instead of typing "Apples" I wanted to reference Cell B2 of the current
worksheet to find the value- Do you know how I could do that?

Gina

 

[Max]

Just point to the cell in the array formula:
=MIN(IF(Data!A1:A6=B2,Data!B1:B6))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik

 

[Gina]

Thank you Max. That works

Just point to the cell in the array formula:
=MIN(IF(Data!A1:A6=B2,Data!B1:B6))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik

 

[Max]

Welcome, Gina
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400, Files:358, Subscribers:55
xdemechanik