What's the difference between MIN and MINIFS?

MIN returns the smallest value in a range with no conditions applied. MINIFS filters the range first (keeping only rows where all criteria match), then returns the minimum. Use MIN for unconditional minimum values; use MINIFS when you need filtering, such as 'minimum hours for Alice' or 'minimum hours for tasks starting in January.'

Can MINIFS handle multiple criteria at once?

Yes. Add additional criteria_range and criteria pairs: =MINIFS(min_range, range1, criteria1, range2, criteria2, ...). All criteria are applied simultaneously using AND logic. In the timesheet example, =MINIFS(E:E, B:B, 'Bob', D:D, '<2024-02-01') finds Bob's tasks due in January, then returns the minimum hours.

Why does MINIFS return #N/A?

No rows satisfy all your criteria. For example, if you search for an owner who doesn't exist or an hours value larger than any logged task, MINIFS has nothing to return. Use COUNTIFS to verify matches exist first, or wrap MINIFS in IFERROR to supply a fallback.

Should I use MINIFS or filter the data manually?

MINIFS is more robust. It recalculates automatically when data changes and works seamlessly in formulas and dashboards. Manual filtering (hiding rows) then MIN is error-prone in shared documents and won't adapt if rows are unhidden. MINIFS is the better choice for dynamic, maintainable reports.

MINIFS function

Q: Why does MINIFS return #N/A?

No rows satisfy all your criteria. For example, if you search for an owner who doesn't exist or an hours value larger than any logged task, MINIFS has nothing to return. Use COUNTIFS to verify matches exist first, or wrap MINIFS in IFERROR to supply a fallback.

Q: Should I use MINIFS or filter the data manually?

MINIFS is more robust. It recalculates automatically when data changes and works seamlessly in formulas and dashboards. Manual filtering (hiding rows) then MIN is error-prone in shared documents and won't adapt if rows are unhidden. MINIFS is the better choice for dynamic, maintainable reports.

Returns the minimum value in a range that meets one or more specified criteria, useful for finding smallest quantities subject to filters.

=MINIFS(min_range, criteria_range1, criteria1, ...)

Generate a MINIFS formula

Describe what you need. The generator will reach for MINIFS where MINIFS is the right tool, and tell you when it is not.

Arguments

How MINIFS reads its arguments

Argument	Required	Description
min_range	Required	The range containing values to evaluate; typically numeric, but MINIFS compares all types and returns the minimum.
criteria_range1	Required	The range to test against criteria1; must be the same size as min_range or a single cell (which broadcasts).
criteria1	Required	A value, expression, or comparison operator (e.g., '>10', 'Alice') matched against criteria_range1; text is case-insensitive.
...	Repeating	Additional criteria_range and criteria pairs (optional); all criteria must be satisfied (AND logic) for a row to be included.

Returns

A number representing the smallest value in min_range matching all criteria, or #N/A if no rows match.

Availability

Excel: 2019+ · Google Sheets: Supported

Worked examples

1. Find minimum hours logged by a specific owner

Task	Owner	Start Date	Due Date	Hours
Logo Design	Alice	2024-01-15	2024-01-20	12
API Integration	Bob	2024-01-16	2024-01-25	28
Testing	Alice	2024-01-17	2024-01-22	8
Documentation	Charlie	2024-01-18	2024-02-01	15
UI Polish	Bob	2024-01-19	2024-01-28	18
Code Review	Alice	2024-01-21	2024-01-23	6

=MINIFS(E2:E7, B2:B7, "Alice")

Result: 6

MINIFS checks the Owner column for 'Alice', finds three matching tasks (Logo Design, Testing, Code Review with 12, 8, and 6 hours), and returns 6 as the smallest value. This answers: What's the shortest task Alice has been assigned?

2. Find minimum hours for tasks starting after a specific date

Task	Owner	Start Date	Due Date	Hours
Logo Design	Alice	2024-01-15	2024-01-20	12
API Integration	Bob	2024-01-16	2024-01-25	28
Testing	Alice	2024-01-17	2024-01-22	8
Documentation	Charlie	2024-01-18	2024-02-01	15
UI Polish	Bob	2024-01-19	2024-01-28	18
Code Review	Alice	2024-01-21	2024-01-23	6

=MINIFS(E2:E7, C2:C7, ">2024-01-18")

Result: 6

MINIFS filters rows where Start Date is after 2024-01-18, matching UI Polish (18 hrs) and Code Review (6 hrs). It returns 6 as the minimum. This identifies the lightest-scoped work started recently.

3. Find minimum hours for tasks owned by Bob with January due dates

Task	Owner	Start Date	Due Date	Hours
Logo Design	Alice	2024-01-15	2024-01-20	12
API Integration	Bob	2024-01-16	2024-01-25	28
Testing	Alice	2024-01-17	2024-01-22	8
Documentation	Charlie	2024-01-18	2024-02-01	15
UI Polish	Bob	2024-01-19	2024-01-28	18
Code Review	Alice	2024-01-21	2024-01-23	6

=MINIFS(E2:E7, B2:B7, "Bob", D2:D7, "<2024-02-01")

Result: 18

MINIFS applies two criteria simultaneously: Owner='Bob' AND Due Date<2024-02-01. This matches API Integration (28 hrs, due Jan 25) and UI Polish (18 hrs, due Jan 28). The function returns 18, the smaller value, identifying Bob's quickest January deadline.

Common errors

Which MINIFS error are you seeing?

Error	Why it happens	How to fix it
#N/A	No rows in the data satisfy all specified criteria. For example, searching for Owner='Diana' when Diana has no tasks, or Hours>100 when all tasks are under 32 hours.	Verify that your criteria match rows in the data. Use COUNTIFS(criteria_range, criteria) to check if matches exist. Alternatively, wrap MINIFS in IFERROR to supply a fallback value like 0 or 'No data found.'
#VALUE!	A criteria_range is a different size than min_range, or a criteria uses an invalid operator or type mismatch. For instance, using MINIFS(E2:E7, B2:B5, 'Alice') with mismatched row counts, or applying a date comparison to a text field.	Ensure all ranges span the same number of rows. Use correct operators: '=', '<', '>', '<=', '>=', '<>' for numbers and dates; bare values like 'Alice' for text equality. Convert types if needed.
#REF!	A reference in min_range or criteria_range points to deleted, moved, or invalid cells. This occurs if rows or columns are deleted after the formula is written, or if a range becomes invalid through worksheet reorganization.	Verify all range references exist and are correct. Use named ranges (Define Name / Name Manager) to make references stable across sheet changes. Re-enter the formula if ranges have shifted.

Tips and when to use something else

Use MINIFS to find the smallest value subject to conditions ('What is the shortest task?'). Use MIN alone if you need no conditions; use MAXIFS if you need the maximum instead.
MINIFS enforces AND logic: all criteria must match simultaneously. For OR logic, combine MINIFS with IF or use IFERROR to test multiple conditions.
Criteria support comparison operators ('>10', '<=100', '<>') for numbers and dates, or plain values ('Bob') for text equality. Text comparisons ignore case.
No matches returns #N/A. Wrap in IFERROR to provide a default: =IFERROR(MINIFS(...), 0) prevents errors in dashboards or downstream formulas.

Related functions

MAXMAX returns the largest numeric value in a range, ignoring text and empty cells. Useful for finding peak inventory, highest prices, or latest dates.MAXIFSReturns the largest value in a range that meets multiple criteria, combining the power of MAX with IF-like filtering.AVERAGEIFSAVERAGEIFS returns the average of values in a range that meet multiple criteria, enabling filtered averages across complex datasets.COUNTIFSCOUNTIFS counts rows where all criteria are met across multiple ranges, useful for filtering inventory and finding matches across conditions.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.

Frequently asked questions

What's the difference between MIN and MINIFS?: MIN returns the smallest value in a range with no conditions applied. MINIFS filters the range first (keeping only rows where all criteria match), then returns the minimum. Use MIN for unconditional minimum values; use MINIFS when you need filtering, such as 'minimum hours for Alice' or 'minimum hours for tasks starting in January.'
Can MINIFS handle multiple criteria at once?: Yes. Add additional criteria_range and criteria pairs: =MINIFS(min_range, range1, criteria1, range2, criteria2, ...). All criteria are applied simultaneously using AND logic. In the timesheet example, =MINIFS(E:E, B:B, 'Bob', D:D, '<2024-02-01') finds Bob's tasks due in January, then returns the minimum hours.
Why does MINIFS return #N/A?: No rows satisfy all your criteria. For example, if you search for an owner who doesn't exist or an hours value larger than any logged task, MINIFS has nothing to return. Use COUNTIFS to verify matches exist first, or wrap MINIFS in IFERROR to supply a fallback.
Should I use MINIFS or filter the data manually?: MINIFS is more robust. It recalculates automatically when data changes and works seamlessly in formulas and dashboards. Manual filtering (hiding rows) then MIN is error-prone in shared documents and won't adapt if rows are unhidden. MINIFS is the better choice for dynamic, maintainable reports.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17