How to Use MINIFS Function in Excel - Practical Guide

Learn to use the MINIFS function to find the minimum value in a range based on multiple criteria. This powerful formula lets you conditionally identify the smallest number that meets specific conditions, essential for data analysis, inventory management, and financial reporting without complex nested formulas.

Why This Matters

MINIFS eliminates the need for complex array formulas and nested functions, making data filtering faster and more readable. It's crucial for financial analysis, reporting, and any scenario requiring conditional minimum calculations.

Prerequisites

•Basic understanding of Excel functions and cell references
•Familiarity with comparison operators (=, >, <, >=, <=)
•Knowledge of single-criterion filtering with MINIFS or basic MIN function

Step-by-Step Instructions

Open your workbook with data

Open Excel and load a spreadsheet containing multiple columns with data (e.g., Product, Region, Price, Quantity). Ensure your data has headers and is organized in contiguous ranges.

Identify your minimum range and criteria ranges

Determine which column contains the values you want to minimize (e.g., Price column) and which columns contain your filter criteria (e.g., Region, Product). Write down the exact cell ranges.

Click on the target cell and type the formula

Select the cell where you want the result. Type: =MINIFS(min_range, criteria_range1, criterion1, criteria_range2, criterion2). Replace brackets with your actual ranges and criteria values.

Enter your criteria values

Specify what values the criteria columns should match (e.g., "North", "Widget"). Use cell references (like B2) or text in quotes (like "North") for static criteria.

Press Enter and verify results

Press Enter to execute the formula. Check that the returned minimum value matches your expected result by manually reviewing filtered data.

Alternative Methods

Using MIN with IF array formula

Use =MIN(IF((criteria_range1=criterion1)*(criteria_range2=criterion2), min_range)) entered as an array formula with Ctrl+Shift+Enter. This works in older Excel versions but is less readable than MINIFS.

Using AGGREGATE with helper columns

Create helper columns that flag rows meeting your criteria, then use MINIFS or MIN on the filtered results. This approach is more transparent for complex multi-step logic.

Filter data manually, then use MIN

Manually filter your data using AutoFilter (Data > Filter) to show only rows meeting criteria, then apply MIN to visible cells using AGGREGATE function with option 15 (ignores filtered rows).

Tips & Tricks

✓Use absolute references ($A$1:$A$100) for your data ranges so formulas don't change when copied to other cells.
✓Create MINIFS formulas in a separate summary table to keep your analysis organized and easy to update.
✓Combine MINIFS with other functions like INDEX/MATCH to find corresponding values in adjacent columns once you locate the minimum.

Pro Tips

★Use wildcard criteria (*) with MINIFS to match patterns: =MINIFS(prices, products, "*widget*") finds minimum price for any product containing 'widget'.
★Nest MINIFS inside other functions like IF to create conditional logic: =IF(MIN(range)=MINIFS(range, criteria1, crit1), "Match", "No match").
★Combine MINIFS with COUNTIFS to verify how many records meet your criteria before analyzing minimum values, ensuring data quality.

Troubleshooting

Formula returns #NAME? error

MINIFS may not be available in your Excel version (requires Excel 2016 or later). Use the MIN/IF array formula alternative or upgrade Excel.

Formula returns #VALUE! error

Check that all criteria_range arguments match the size of min_range. Verify data types are consistent (text vs. numbers) between criteria and data columns.

Formula returns unexpected high value or #NUM! error

No rows match your criteria. Verify criteria values are spelled correctly, check for leading/trailing spaces in text criteria, and ensure criteria actually exist in your data.

Formula works but seems slow with large datasets

Use named ranges instead of cell references for better performance. Consider breaking data into smaller tables or using pivot tables for massive datasets (100k+ rows).

Related Excel Formulas

MAXIFS MIN IF SUMIFS COUNTIFS INDEX MATCH

Frequently Asked Questions

What Excel versions support MINIFS?

MINIFS is available in Excel 2016, Excel 2019, Excel 365, and Google Sheets. For Excel 2013 and earlier, use the MIN/IF array formula alternative.

Can MINIFS handle multiple criteria?

Yes, MINIFS can handle unlimited criteria pairs. Each additional criterion is added as another criteria_range and criterion pair: =MINIFS(min_range, criteria_range1, criterion1, criteria_range2, criterion2, criteria_range3, criterion3, etc.).

How do I use MINIFS with date ranges?

Use comparison operators (>, <, >=, <=) with dates: =MINIFS(price_range, date_range, ">="&DATE(2024,1,1), date_range, "<="&DATE(2024,12,31)). This finds minimum prices for dates in 2024.

What's the difference between MINIFS and MIN with IF?

MINIFS is simpler, more readable, and performs faster than array formulas. MIN/IF requires array formula entry (Ctrl+Shift+Enter) and is harder to maintain. MINIFS is preferred in modern Excel versions.

Can MINIFS work with wildcard criteria?

Yes, use asterisks (*) to match partial text patterns in criteria: =MINIFS(prices, products, "*shirt*") returns the minimum price for any product containing 'shirt' in its name.