Practice free

MINIFS function

MINIFS returns the smallest value in a range, counting only the rows that meet every condition you set, such as the lowest price in one category. If no rows match it returns 0, which can look like a real minimum.

Example

Read it as a sentence: find the smallest Sales value in O2:O7 where the Region column in M2:M7 says North, which gives 100.

Practice this in the app

Syntax

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Arguments in [square brackets] are optional.

min_range (required)
The cells to take the minimum from.
criteria_range1 (required)
Range tested against the first condition; same size as min_range.
criteria1 (required)
The condition, such as "b" or "<50".
criteria_range2, criteria2 (optional)
Optional extra range and condition pairs; all must be met.

Returns: The smallest value in min_range among rows meeting every condition.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=MINIFS(Data!O2:O7,Data!M2:M7,"North") 100 One criterion
=MINIFS(Data!O2:O7,Data!M2:M7,"North",Data!N2:N7,"B") 200 Two criteria
=MINIFS(Data!O2:O7,Data!M2:M7,"West") 0 No match returns 0
=MINIFS(Data!O2:O7,Data!M2:M7,{"North";"South"}) 100 Array criteria spill
=MINIFS(Data!O2:O7,Data!M2:M7,"*th") 75 Wildcard
=MINIFS(Data!O2:O7,Data!M2:M5,"North") #VALUE! Mismatched sizes

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Mismatched range sizes give #VALUE!.
  • Returns 0 if no rows match.
  • Not in Excel 2016 or earlier perpetual versions.
  • Excel refuses the formula outright if you type an array constant such as {1,2,3} where a range is expected; point it at cells instead (measured in Excel 16.0.20527).

Where it works

Excel
MINIFS arrived in Excel 2019. Older versions do not have it.
Google Sheets
Same name, same behaviour for everyday use.

Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .