Master MINIFS in Google Sheets: Multi‑Criteria Minimums

What MINIFS does in Google Sheets

MINIFS is a powerful function that finds the smallest numeric value in a designated min_range that meets all supplied criteria across corresponding criteria ranges. When working with datasets in Google Sheets, you often need to extract a minimum value under constraints such as date windows, category labels, and numeric thresholds. MINIFS supports multiple criteria, allowing you to combine conditions like dates and text in a single formula, without creating intermediate columns. This makes it ideal for dashboards and ad‑hoc analysis where speed and readability matter.

What this does: it considers rows where A2:A100 is greater than 0 and C2:C100 is on or before December 31, 2026, then returns the smallest value from B2:B100 that satisfies both.

What MINIFS does in Google Sheets

MINIFS is a concise way to find the minimum value under multiple logical constraints. The function evaluates each row against all provided criteria ranges and criteria pairs; only rows that satisfy every condition contribute to the final minimum. This is especially useful when you need to filter by a date range, a category, and a numeric limit all at once. Unlike chaining helpers or nested IF statements, MINIFS keeps your sheet lean and readable.

Tip: For text fields, you can use wildcards like * to match partial strings (e.g., "Widget*").

Syntax and parameters

MINIFS(min_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

• min_range (required): The numeric range from which the minimum is returned.
• criteria_range1 (required): The range to evaluate against the first criterion.
• criterion1 (required): The condition for the first criteria range (e.g., ">0", "Widget*", "<>0").
• Additional pairs: Optional more criteria ranges and their corresponding criteria.

Example with two criteria shows how to constrain by both a category and a date.

Basic examples

Let’s use a simple dataset where column A = Category, column B = Amount, and column C = Date. You can pull the smallest amount for a specific category:

This returns the smallest value in B2:B10 where the corresponding A2:A10 cell equals "Apples". You can add a second criterion for dates:

Now you’re filtering by category and a date window simultaneously.

Working with multiple criteria and data types

MINIFS handles numeric, date, and text criteria. For example, find the smallest sale amount for a product whose name starts with "Widget" and occurs in a specific region:

You can also mix date criteria with numeric thresholds:

If your dataset uses named ranges like min_sales and product and region, you can write clearer formulas:

Wildcards and concatenation with dates enable sophisticated filtering without extra steps.

Handling errors and edge cases

MINIFS can produce an error or empty result when no rows meet the criteria. A common pattern is to wrap the formula in IFERROR to provide a friendly message or fallback value:

Edge cases to consider:

• Empty min_range: returns an error; guard with IFERROR or input checks.
• Overly broad criteria: may cause long calculation times on large datasets.
• Mixed data types: ensure the ranges are consistent (numbers with numbers, dates with dates).

Pro tip: keeping your data types consistent reduces surprising results.

Alternative approaches for complex criteria

If your criteria become highly dynamic or require non-contiguous ranges, you can use FILTER with MIN to emulate MINIFS behavior:

Both approaches yield the same end result, but FILTER + MIN is often easier to adapt when you need to combine with other array operations. You can also use BYROW or MAP to construct more elaborate criteria, though MINIFS remains the simplest option for straightforward multi-criteria minimums.

For large datasets, consider narrowing the ranges or using named ranges to improve calculation speed.

Practical templates you can reuse

Template 1: Multi-criteria minimum with a single product filter

Template 2: Named ranges for readability

Tips for templates:

• Define named ranges like min_sales, product_col, and date_col to make formulas self-explanatory.
• Add comments in your sheet to remind readers what each range represents.
• Use dynamic named ranges to auto-expand as data grows.

Performance considerations and best practices

In practice, MINIFS is fast for typical worksheet sizes, but performance can degrade if you reference entire columns (e.g., B:B) with many criteria. To optimize:

• Use precise ranges that match your data (e.g., B2:B1000 instead of B2:B).
• Prefer named ranges for long formulas to improve readability and maintainability.
• Keep data types consistent across criteria ranges (numbers with numbers, dates with dates).
• If you need to analyze live data, consider using FILTER + MIN for highly dynamic criteria, since some scenarios can trigger recalculation more often.

When documenting your sheet, note the exact ranges and criteria so others can audit and reuse the logic with minimal surprises.