Master the MINIFS Function: Find Minimum Values with Advanced Criteria
=MINIFS(min_range, criteria_range1, criteria1, ...)The MINIFS function is a powerful Excel tool that allows you to find the minimum value in a range based on one or more criteria. Unlike the basic MIN function, which simply returns the smallest value in a range, MINIFS enables you to apply conditional logic to your calculations, making it essential for complex data analysis scenarios. This function is particularly valuable when working with large datasets where you need to identify the lowest value that meets specific conditions, such as finding the minimum price for a particular product category or the lowest sales figure for a specific region. MINIFS was introduced in Excel 2016 and is now available across Excel 2019 and Microsoft 365. It represents a significant advancement in Excel's conditional analysis capabilities, allowing business professionals, data analysts, and spreadsheet users to perform sophisticated calculations without relying on array formulas or helper columns. Understanding how to effectively use MINIFS can dramatically improve your data analysis efficiency and enable you to derive meaningful insights from complex datasets with minimal effort.
Syntax & Parameters
The MINIFS function follows this syntax: =MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). The min_range parameter is required and specifies the range containing the values from which you want to find the minimum. This is the range that will be evaluated to determine the smallest value. The criteria_range1 parameter defines the first range to be evaluated against your criteria, and criteria1 specifies the actual condition that must be met. You can add multiple criteria pairs, making MINIFS incredibly flexible for complex scenarios. Each additional criteria_range and criteria pair works with AND logic, meaning all conditions must be true simultaneously for a value to be considered. Important tips: ensure all ranges have the same size to avoid errors, use absolute references ($) for criteria ranges when copying formulas, and remember that MINIFS returns an error if no values meet your criteria. The function supports various criteria types including numbers, text, dates, and logical operators like "<", ">", "<>", and "=".
min_rangecriteria_range1criteria1Practical Examples
Finding Minimum Price by Product Category
=MINIFS(C:C, B:B, "Electronics")This formula searches the Price column (C) and returns the minimum value where the Category column (B) equals 'Electronics'. It's useful for identifying competitive pricing strategies and cost analysis.
Minimum Sales by Region and Quarter
=MINIFS(D:D, B:B, "North", C:C, "Q3")This formula uses two criteria: Region (B) must be 'North' AND Quarter (C) must be 'Q3'. The minimum value from the Sales column (D) meeting both conditions is returned. This helps identify underperforming periods.
Lowest Employee Salary by Department and Employment Status
=MINIFS(E:E, C:C, "Marketing", D:D, "Full-time")This formula evaluates the Salary column (E) and returns the minimum where Department (C) is 'Marketing' AND Employment Status (D) is 'Full-time'. Useful for compensation analysis and equity reviews.
Key Takeaways
- MINIFS finds the minimum value in a range based on multiple criteria using AND logic, available in Excel 2016 and later versions
- The function requires min_range, criteria_range1, and criteria1 as minimum parameters, with unlimited additional criteria pairs supported
- MINIFS is superior to array formulas for conditional minimum calculations, offering better readability, performance, and ease of use
- Combine MINIFS with error handling (IFERROR), INDEX/MATCH for data retrieval, and other functions like SUMIFS for advanced analytics
- Always use absolute references for range parameters when copying formulas, and test criteria with COUNTIFS before implementing complex calculations
Pro Tips
Use absolute references ($) for your criteria ranges when copying formulas across multiple cells. For example: =MINIFS($C$2:$C$100, $B$2:$B$100, A2) allows you to copy the formula down while keeping the data range fixed but updating the criteria reference.
Impact : Prevents broken references and ensures formula consistency when scaling across worksheets, saving time and reducing errors.
Combine MINIFS with INDIRECT for dynamic range selection. This allows you to create formulas that automatically adjust based on user input or worksheet changes: =MINIFS(INDIRECT(A1), INDIRECT(B1), C1)
Impact : Enables highly flexible, user-driven dashboards and reports that require no manual formula updates when data structure changes.
For performance optimization on large datasets (100,000+ rows), specify exact ranges instead of entire columns: =MINIFS(C2:C100000, B2:B100000, "Electronics") rather than =MINIFS(C:C, B:B, "Electronics")
Impact : Significantly improves calculation speed and reduces memory consumption, making your workbook more responsive and professional.
Test your criteria with COUNTIFS first to verify that your conditions are matching data correctly before using MINIFS. For example: =COUNTIFS(B:B, "Electronics", D:D, ">100") confirms how many records match before finding the minimum.
Impact : Prevents unexpected results and helps debug complex multi-criteria formulas quickly, ensuring accuracy in your analysis.
Useful Combinations
MINIFS with IFERROR for Error Handling
=IFERROR(MINIFS(C:C, B:B, "Electronics", D:D, ">100"), "No matching records")Combining MINIFS with IFERROR provides graceful error handling. If no values meet the criteria, instead of displaying #NUM!, it shows a custom message. This improves user experience and prevents formula errors from breaking reports.
MINIFS with SUMIFS for Conditional Analysis
=SUMIFS(E:E, B:B, "North", C:C, "Q1") / MINIFS(D:D, B:B, "North", C:C, "Q1")This combination calculates a ratio by dividing total sales by the minimum value in a category. Useful for performance metrics and comparative analysis across regions or departments.
MINIFS with INDEX and MATCH for Row Retrieval
=INDEX(A:A, MATCH(MINIFS(C:C, B:B, "Electronics"), C:C, 0))This powerful combination finds not just the minimum value, but also retrieves the corresponding product name. It's essential when you need to identify which item has the minimum price, not just the price itself.
Common Errors
Cause: This error occurs when the min_range contains mixed data types or when criteria values don't match the data type in the criteria_range. For example, using a text criteria against a numeric range.
Solution: Verify that all values in min_range are numeric. Check that criteria values match the data type in criteria_range. Use VALUE() function to convert text numbers to actual numbers if needed: =MINIFS(C:C, B:B, VALUE("100"))
Cause: This error indicates that one or more of your range references are invalid or broken. This commonly happens when rows or columns are deleted, or when you reference ranges that don't exist.
Solution: Verify all range references are correct and exist in your worksheet. Use named ranges for better stability. Avoid deleting columns or rows that are referenced in your formula. Consider using absolute references: =MINIFS($C$2:$C$100, $B$2:$B$100, "Electronics")
Cause: This error occurs when no values in the min_range meet all the specified criteria, or when the function encounters an invalid numeric operation.
Solution: Check that your criteria actually exist in the data. Verify spelling and case sensitivity in text criteria. Use IFERROR to handle cases where no criteria match: =IFERROR(MINIFS(C:C, B:B, "NonExistent"), "No data found")
Troubleshooting Checklist
- 1.Verify that min_range and all criteria_ranges have the same number of rows and are properly aligned
- 2.Check that criteria values match the exact data type and spelling in the source data (watch for extra spaces, case sensitivity)
- 3.Confirm that at least one value in min_range actually meets all specified criteria; use COUNTIFS to verify
- 4.Ensure ranges don't include header rows unless headers are text and won't interfere with numeric calculations
- 5.Test with IFERROR to identify if #NUM! errors occur due to no matching criteria rather than formula syntax issues
- 6.Verify that numeric criteria are truly numbers and not text formatted as numbers (use VALUE() function if needed)
Edge Cases
MINIFS with empty cells in criteria_range
Behavior: Empty cells are treated as criteria matches if the criteria is an empty string (""). Otherwise, they're ignored. If min_range has corresponding empty cells, they're treated as zero in numeric comparisons.
Solution: Use COUNTBLANK to identify empty cells and adjust criteria accordingly. Consider filtering out empty rows before applying MINIFS: =MINIFS(C:C, B:B, "<>", D:D, ">0")
Be cautious with datasets containing intentional empty cells that might skew minimum calculations.
MINIFS with all negative numbers in min_range
Behavior: MINIFS correctly identifies the least negative number (closest to zero) as the minimum. For example, -5 is greater than -10, so -5 would be returned.
Solution: This is correct behavior; no action needed. Use ABS() if you need the minimum absolute value: =MINIFS(ABS(C:C), B:B, "Category")
Verify your business logic when working with negative numbers; ensure 'minimum' means what you intend.
MINIFS with criteria containing special characters or operators
Behavior: Criteria like "<100", ">50", or "<>0" work as expected with comparison operators. Text criteria with special characters like asterisks (*) trigger wildcard matching.
Solution: For literal asterisk matching, escape with tilde (~): =MINIFS(C:C, B:B, "~*"). For complex criteria, use multiple MINIFS functions combined with MIN.
Always test special character criteria carefully to ensure they produce expected results.
Limitations
- •MINIFS uses AND logic exclusively; it cannot directly apply OR conditions. To find minimum where criteria1 OR criteria2 is true, you must use multiple MINIFS functions wrapped in MIN function.
- •MINIFS returns #NUM! error if no values meet all specified criteria, requiring IFERROR wrapper for user-friendly error handling in production reports.
- •The function cannot directly work with complex nested criteria or custom functions; criteria must be static values, cell references, or simple expressions like ">100" or DATE functions.
- •MINIFS is unavailable in Excel versions prior to 2016; organizations using Excel 2013 or earlier must use array formulas or database functions (DMIN) as alternatives.
Alternatives
Compatibility
✓ Excel
Since 2016
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)✓Google Sheets
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Google Sheets supports MINIFS with identical syntax and functionality to Excel 2016+. Works seamlessly with Google Sheets' calculation engine.
✓LibreOffice
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)