A B
1 Fruit Amount
2 Apple 10
3 Banana 12
4 Apple 9
5 Pineapple 8
6 Apple 11
7
8 Criteria
9 Fruit Amount
10 Apple
11
12 Result
10 Apple 9 Formula =DMIN($A$1:$A6,B1,A9:A10)
My question is insted of using Criteria geting form the sheet i want to
manual input the criteria in formulla like =DMIN($A$1:$A6,B1,"Fuite"="Apple")
since i have a long sheet and i will fix the formula in each cell. also
there are many items so i do not want to change the criteria for every items.
is there anyway? How can i do it? Can anyone help?

Dummy wrote...
....
My question is insted of using Criteria geting form the sheet i
want to manual input the criteria in formulla like

=DMIN($A$1:$A6,B1,"Fuite"="Apple")

since i have a long sheet and i will fix the formula in each cell.
also there are many items so i do not want to change the criteria
for every items. is there anyway? How can i do it? Can anyone help?

Don't use DMIN. Use an array formula like

=MIN(IF($A$2:$A$6="Apple",$B$2:$B$6))

You could use DMIN as the formula for a data table, but it's still
klunky.

TNANKS A LOT:o)
It did not work at first time then i realiese there must be small mistake
and it was array nesting { } by pressing Ctrl+Shift+Enter. It work grate.
Thanks once again for solving my problem i was working for it more than a
week but could not find the right solution.


"Harlan Grove" wrote:

Dummy wrote...
....
My question is insted of using Criteria geting form the sheet i
want to manual input the criteria in formulla like

=DMIN($A$1:$A6,B1,"Fuite"="Apple")

since i have a long sheet and i will fix the formula in each cell.
also there are many items so i do not want to change the criteria
for every items. is there anyway? How can i do it? Can anyone help?

Don't use DMIN. Use an array formula like

=MIN(IF($A$2:$A$6="Apple",$B$2:$B$6))

You could use DMIN as the formula for a data table, but it's still
klunky.