MINIFS
Finds the smallest number among the rows that match the conditions: the lowest price for a product across suppliers.
MINIFS(min_range, criteria_range1, criteria1, ...)- min_range
- The numbers among which to find the smallest, for example prices in C2:C8.
- criteria_range1
- Where to check the first condition, for example product names in A2:A8.
- criteria1
- The first condition. You can then add a second range and a second condition the same way.
Return value
The smallest number among the rows that matched all conditions. If there are no such rows, the function returns 0.
Examples
Best price across suppliers
fx
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
Product
Supplier
Price, $
Product
Best price, $
Copy paper
Office Market
420
Copy paper
Toner
Office Market
3900
Toner
Copy paper
Paper Plus
385
Stapler
Stapler
Paper Plus
610
Toner
Print Service
3650
Copy paper
Print Service
399
Stapler
Office Market
540
Sheet1
Details
MINIFS takes the range in which to look for the minimum first, and then pairs of “where to check and what condition”. The function skips empty cells and text in the number range.
If no row matches the conditions, the function returns 0, not an error. It’s available in Excel 2019, 2021 and Microsoft 365.
Common mistakes
- The product isn’t in the list or its name has a typo: the function returns 0, and it looks as if the product is free.
- A price is stored as text, for example “385 USD”: the function skips it, and the minimum turns out different.
- The order of arguments: the range of numbers comes first, not last as in SUMIF.