Bespoke Microsoft Office Development 
 Consultancy|

Excel

|Word|PowerPoint|Outlook|VBA|Tools|Newsletter  
 Excel > Functions > Database > DMIN

 

DMIN(database, field, criteria)

 
 Returns the smallest number in a database column satisfying certain conditions.

 databaseThe range of cells that make up the database or list.
 fieldThe column name or number indicating which column to use.
 criteriaThe range of cells that contain the conditions.

 REMARKS
 
  • Any cells containing text, logical values and errors are NOT INCLUDED. See Examples 7, 8 and 15.
  •  
  • The "database" can be a cell reference or a named range.
  •  
  • The "database" range of cells must contain column headings in the first row. See Example 19.
  •  
  • The "field" can be a column label, a column number (left to right) or a cell reference.
  •  
  • The "field" label is not case sensitive. See Example 12.
  •  
  • If "field" is left blank, then #VALUE! is returned. See Example 20.
  •  
  • If the "field" column contains some text entries, then these are ignored. See Example 7.
  •  
  • The "criteria" must include at least one column heading and one cell below the column heading. See Example 16.
  •  
  • If "criteria" is left blank, then #VALUE! is returned. See Example 21.
  •  
  • The "criteria" must be in a contiguous block of adjacent cells.
  •  
  • The "criteria" can be anywhere on the active sheet, although it is recommended not to put it below the data, in case more data is added later on.
  •  
  • Make sure the "criteria" and "database" ranges do not overlap and that they are always separated by at least one blank row or column.
  •  
  • To perform an operation on an entire column in a database, enter a blank cell below the column heading in the "criteria" range. See Example 11.
  •  
  • If no rows match the criteria, then 0 is returned. See Example 14.
  •  
  • Example 1 - What is the smallest "Age" for all the people with the name "John" using a field label.
  •  
  • Example 2 - What is the smallest "Age" for all the people with the name "John" using a field number.
  •  
  • Example 3 - What is the smallest "Salary" for all the people with either the name "John" or the name "James".
  •  
  • Example 4 - What is the smallest "Age" for all the people who are less than 22 years old.
  •  
  • Example 5 - What is the smallest "Age" for all the people who are less than 22 years old or more than 18 years old.
  •  
  • Example 6 - What is the smallest "Salary" for all the people who are less than 22 years old and earn more than £20,000.
  •  
  • Example 7 - What is the smallest "Age" for all the people who earn more than £20,000.
  •  
  • Example 8 - What is the smallest "Salary" for all the people who earn more than £20,000.
  •  
  • Example 9 - What is the smallest "Salary" for all the people with the name "James" who are older than 18 years old.
  •  
  • Example 10 - What is the smallest "Salary" for all the people with either the name "James", who are less than 22 years old who earn more than £20,000 or have the name "James" and are older than 18 years old.
  •  
  • Example 11 - What is the smallest "Salary" for all the people. Putting a blank cell below the column heading will refer to the entire column.
  •  
  • Example 12 - This is the same as Example 11 but with the field label in uppercase.
  •  
  • Example 13 - If the "criteria" refers to a range of empty cells, then the entire column from the "database" range is used.
  •  
  • Example 14 - If the "criteria" you specify does not return any rows, then 0 is returned.
  •  
  • Example 15 - If the "field" you are trying to count is not numeric in this case "Name", then 0 is returned because any text entries are ignored.
  •  
  • Example 16 - If the "criteria" only includes a column heading, then #VALUE! is returned.
  •  
  • Example 17 - If the "database" range only includes column headings, then #VALUE! is returned.
  •  
  • Example 18 - If the "criteria" refers to a single empty cell, then #VALUE! is returned.
  •  
  • Example 19 - If the "database" range does not include column headings, then #VALUE! is returned.
  •  
  • Example 20 - If "field" is left blank, then #VALUE! is returned.
  •  
  • Example 21 - If "criteria" is left blank, then #VALUE! is returned.
  •  
  • Example 22 - If "field" does not match one of the column headings, then #VALUE! is returned.
  •  
  • For more examples of how to specify your database criteria, please refer to the Database > Specifying Database Criteria page.
  •  
  • It is possible to use the data from a pivot table. When the "database" range refers to a cell inside a pivot table, the calculation is only performed on the data currently displayed in the pivot table.

  •  EXAMPLES
     
     ABCD
    1=DMIN(B1:D10,"Age",B12:B13) = 19NameAgeSalary
    2=DMIN(B1:D10,2,B12:B13) = 19John1918,000
    3=DMIN(B1:D10,"Salary",B12:B14) = 18,000Mark2024,000
    4=DMIN(B1:D10,"Age",C12:C13) = 17David1716,500
    5=DMIN(B1:D10,"Age",C12:C14) = 17James2220,000
    6=DMIN(B1:D10,"Salary",C12:D13) = 24,000 1819,500
    7=DMIN(B1:D10,"Age",D12:D13) = 20Nick2124,000
    8=DMIN(B1:D10,"Salary",D12:D13) = 24,000Matthewtext26,500
    9=DMIN(B1:D10,"Salary",B16:D17) = 20,000Jamie1718,500
    10=DMIN(B1:D10,"Salary",B12:D14) = 20,000Mark2429,000
    11=DMIN(B1:D10,"Salary",D16:D17) = 16,500   
    12=DMIN(B1:D10,"SALARY",D16:D17) = 16,500NameAgeSalary
    13=DMIN(B1:D10,"Salary",B19:B20) = 16,500John<22>20000
    14=DMIN(B1:D10,"Salary",B12:D13) = 0James>18 
    15=DMIN(B1:D10,"Name",C12:C13) = 0   
    16=DMIN(B1:D10,"Age",C12) = #VALUE!NameAgeSalary
    17=DMIN(B1:D1,"Age",B12:B14) = #VALUE!James>18 
    18=DMIN(B1:D10,"Salary",B19) = #VALUE!   
    19=DMIN(B2:D10,"Age",B12:B13) = #VALUE!   
    20=DMIN(B1:D10,,C12:D13) = #VALUE!   
    21=DMIN(B1:D10,"Age",) = #VALUE!   
    22=DMIN(B1:D10,"some text",B12:B13) = #VALUE!   
     

     Functions - D | Index - D | Office Online 2013 | 2010 | 2007 | 2003 

     © Better Solutions Limited 21-Apr-2014Top