Excel MINIFS function

Summary

The Excel MINIFS function calculates the minimum value in a range based on one or more criteria. It extends the functionality of the MIN function by allowing for conditional filtering of data. MINIFS is particularly useful for scenarios requiring the identification of the lowest number that meets specific conditions, such as the minimum sales in a certain region or the lowest score achieved by students in a particular subject.
Syntax
				
					=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
				
			
  • min_range: The range of cells from which the minimum value will be determined.
  • criteria_range1: The range of cells to evaluate with the first criteria.
  • criteria1: The condition that cells in the first criteria range must meet.
  • [criteria_range2, criteria2], …: Additional ranges and their corresponding criteria.
Return value
The smallest number in min_range that meets all the specified criteria.

How to use

Define the range from which to find the minimum value (min_range), then specify one or more pairs of criteria ranges and criteria. MINIFS assesses the criteria against the respective ranges and returns the lowest value that satisfies all conditions.

Examples

Simple MINIFS
Finding the Lowest Sales in a Region: To identify the minimum sales figure in the West region.
				
					=MINIFS(B2:B100, A2:A100, "West")
				
			
If B2:B100 contains sales figures and A2:A100 lists regions, aiming to find the lowest sales number in the “West” region, MINIFS returns the minimum sales figure exclusively from those tagged as “West”.
MINIFS with Multiple Criteria
Determining the Minimum Score for a Specific Course: Calculating the lowest score in Chemistry by sophomores.
				
					=MINIFS(C2:C100, B2:B100, "Chemistry", D2:D100, "Sophomore")
				
			
Assuming C2:C100 holds student scores, B2:B100 the courses, and D2:D100 the grade levels, this formula finds the lowest Chemistry score among sophomore students.
MINIFS for Date Range
Identifying the Lowest Sales in a Specific Month: To find the minimum sales figure achieved in February 2022.
				
					=MINIFS(B2:B100, A2:A100, ">=2/1/2022", A2:A100, "<=2/28/2022")
				
			
This calculates the lowest sales figure in B2:B100 for sales that occurred in February 2022, based on dates listed in A2:A100.
MINIFS Across Different Data Types
Finding the Minimum Value Excluding Certain Types: Identifying the lowest test score that isn’t labeled as a preliminary attempt.
				
					=MINIFS(C2:C100, D2:D100, "<>Preliminary")
				
			
For C2:C100 containing test scores and D2:D100 indicating if a test was “Preliminary”, this formula returns the lowest score among the final attempts only.

Additional Notes

  • MINIFS can handle multiple conditions for refined data querying, providing flexibility in extracting specific minimum values.
  • Introduced in Excel 2016, MINIFS is not available in earlier versions of Excel. For those versions, similar functionality can be achieved using array formulas with MIN and IF functions combined.

Related Functions

Excel MAX function

The Excel MAX function finds the highest value in a set, key for identifying peak data points.

Excel MAXIFS function

The Excel MAXIFS function determines the maximum value among cells specified by multiple criteria, optimizing targeted analyses.

Excel MIN function

The Excel MIN function identifies the lowest value in a data set, crucial for determining minimums in analyses.

Content Navigation