To find the minimum value in Excel based on a criteria, use the MINIFS function: =MINIFS(min_range, criteria_range1, criteria1). For example, =MINIFS(B2:B20, A2:A20, "East") returns the smallest sales figure for the East region. If your Excel version lacks MINIFS (pre-2019), use an array formula with MIN and IF: =MIN(IF(A2:A20="East", B2:B20)) entered with Ctrl+Shift+Enter.
Why MINIFS Is the Go-To Function
MINIFS was introduced in Excel 2019 and is available in Microsoft 365. It works like SUMIFS or AVERAGEIFS but returns the smallest numeric value that meets one or more conditions. The syntax is straightforward: =MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). The min_range contains the values you want to evaluate, and each criteria range is paired with a condition. The function ignores blank cells and text values in the min_range, but if no rows meet the criteria, it returns 0 (which can be misleading, so check for that).
Using Array Formulas for Older Excel Versions
If you’re on Excel 2016 or earlier, MINIFS doesn’t exist. The classic approach is an array formula: =MIN(IF(criteria_range=criteria, min_range)). With names in A2:A20 and sales in B2:B20, to find the minimum for “East”, enter =MIN(IF(A2:A20="East", B2:B20)) and press Ctrl+Shift+Enter (on Mac, Cmd+Shift+Enter). Excel wraps the formula in curly braces {}. The IF function evaluates each cell in A2:A20; where it matches “East”, it returns the corresponding sales value, otherwise FALSE. MIN ignores FALSE values. This works, but it’s less efficient and can be tricky if you forget the array entry.
How to Choose Between MINIFS and Array Formulas
- Use MINIFS if you have Excel 2019, Excel 2021, or Microsoft 365. It’s simpler, faster, and recalculates more efficiently.
- Use the array formula if you must support older versions (2016 and earlier) or if you’re sharing the workbook with users on those versions.
- Consider
AGGREGATEas an alternative:=AGGREGATE(15, 6, B2:B20/(A2:A20="East"), 1). The 15 means SMALL, 6 ignores errors, and dividing by the condition creates errors for non-matches. This works in all versions and doesn’t require Ctrl+Shift+Enter.
Handling Multiple Criteria
Both MINIFS and the array formula can handle multiple conditions. For MINIFS, just add more pairs: =MINIFS(B2:B20, A2:A20, "East", C2:C20, ">100") finds the minimum sales in the East region where the quantity in C2:C20 is greater than 100. For the array formula, use multiplication: =MIN(IF((A2:A20="East")*(C2:C20>100), B2:B20)). The multiplication acts as an AND operator. Remember to press Ctrl+Shift+Enter for the array version.
Common Pitfalls and How to Avoid Them
- Text vs. numbers: If your criteria is a number, don’t put quotes:
=MINIFS(B2:B20, C2:C20, 100)matches exactly 100, not text “100”. - Wildcards:
MINIFSsupports wildcards for text criteria. Use*for any sequence and?for a single character. For example,=MINIFS(B2:B20, A2:A20, "E*")matches “East” and “Eastern”. - Blank cells: Blank cells in the min_range are ignored, but if all matching rows are blank, the result is 0. Check with
COUNTIFSto ensure matches exist. - Errors in data: If any cell in the min_range contains an error (like #DIV/0!),
MINIFSignores it, but the array formula will return that error. UseIFERRORorAGGREGATEto handle.
Real-World Example: Sales Data
Suppose you have a sales table with columns: A (Region), B (Salesperson), C (Sales Amount). Rows 2 to 20 contain data. To find the lowest sales amount for the “West” region, use: =MINIFS(C2:C20, A2:A20, "West"). If the West region has sales amounts 1500, 2300, and 980, the formula returns 980. For the lowest sales in the West region by a specific salesperson “Jane”, use: =MINIFS(C2:C20, A2:A20, "West", B2:B20, "Jane").
Comparing MINIFS with Other Functions
MINIFS is similar to MAXIFS (for maximum) and AVERAGEIFS (for average). The array formula approach works for all of them by swapping MIN with MAX or AVERAGE. AGGREGATE is more flexible because it can ignore errors and hidden rows, but it’s less intuitive. SUMPRODUCT can also be used for minimum, but it’s more complex and not recommended. For dynamic arrays in Microsoft 365, you could use MIN(FILTER(...)): =MIN(FILTER(C2:C20, A2:A20="West")) which spills and is easy to read.
Frequently Asked Questions
Can I use MINIFS with a date criteria?
Yes, dates are numbers in Excel. Use a date in quotes or a cell reference: =MINIFS(B2:B20, A2:A20, ">"&DATE(2024,1,1)) finds the minimum after Jan 1, 2024. Or reference a cell: =MINIFS(B2:B20, A2:A20, ">"&D1) where D1 contains a date.
Why does my array formula return 0 instead of the minimum?
That usually means no rows meet the criteria, or the min_range contains text that MIN ignores. The IF returns FALSE for non-matches, and MIN ignores FALSE, but if all are FALSE, MIN returns 0. Use COUNTIF to verify matches exist.
Does MINIFS work in Excel for the web?
Yes, MINIFS is available in Excel for the web as long as you have a Microsoft 365 subscription. The syntax is identical. Array formulas also work, but you must press Ctrl+Shift+Enter in the web version too.
How do I find the second smallest value based on criteria?
Use SMALL with an array formula: =SMALL(IF(A2:A20="East", B2:B20), 2) entered with Ctrl+Shift+Enter returns the second smallest. For MINIFS, there’s no direct way; you’d need to use AGGREGATE or array formulas.
Can I use MINIFS with a cell reference as a criteria?
Absolutely. Instead of typing “East”, use a cell reference: =MINIFS(B2:B20, A2:A20, D1) where D1 contains “East”. This makes the formula dynamic.
Related
- Excel Gradebook Template for Teachers: How to Use
- Excel Group Lake Jackson TX: What It Is & Services
- Excel Flexi Timesheet Template UK: How to Build One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Page Break in Excel Shortcut: Insert and Adjust BreaksIn Excel for Microsoft 365 on Windows, select the row, column, or cell where the printed page should…
- Excel Consolidate Button: Where It Is and How to Use ItIn Excel for Microsoft 365 on Windows, find the Consolidate button on the Data tab, in the Data…
- Strikethrough in Excel: Keyboard Shortcut and How to Use ItTo apply or remove strikethrough in Excel for Microsoft 365 on Windows, select the cell or cells and…
- Clear Cache in Outlook for Windows 10: Step-by-StepTo clear cache in classic Outlook on Windows 10, first choose which cache is causing the problem: use…
- Excel Shortcut to Remove Gridlines: Windows and MacIn Excel for Microsoft 365 on Windows, press Alt, then W, V, G to toggle gridlines off for…
- Examples of Ratings in Excel: Scores, Stars, and RulesExamples of ratings in Excel include 1-to-5 customer scores, star displays, letter-grade categories, and color-coded performance scales. For…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.