Min with criteria

[josh62400]

I currently have a 2 worksheets in one workbook. The first is a "summery" of the second. The second list all of the raw materials that have been recieved with the date recieved and their physical properties (the same raw material may be listed several times with different dates and physical properties.) We'll call it WKSHT 2. For example, some of the physical properties on WKSHT 2 are as follows: width, wgt, thickness... I need to find the min and max thickneses and widths for each particular raw material on WKSHT 2 and post them on WKSHT 1. The DMIN function decribes what I want to do but I cant get to work correctly.
Please Help.
Thanks.

 

[Brenkenathan]

like this

MIN(Sheet1!A1:Sheet1!A5)
MAX(Sheet!A1:Sheet1!A5)

Replace the "Sheet1!" with your worksheet name followed by a ! and the cells and BOOM you got it

 

[Asala42]

I'm assuming you're in a situation like the following:

On a DataSheet-
A2:A100 contains widgit names (some repeating)
B2:B100 contains measurements (say widths)

On a Summary sheet-
Your trying to find the min and max width (for example) for a list of widgets.

If true, you can use this array formula:

{=MIN(IF(DataSheet!$A$2:$A$100=Summary!E2,DataSheet!$B$2:$B$100))}

and

{=MAX(IF(DataSheet!$A$2:$A$100=Summary!E2,DataSheet!$B$2:$B$100))}

Where "Summary!E2" contains a Widgit name on the summary sheet.

-Note that as an array formula you'll need to hit Crtl+Shift+Enter (instead of just Enter) after typing the formula.

HTH
This message was edited by Asala42 on 2002-09-20 11:39