The MINIFS function in Excel is a powerful tool that allows users to determine the minimum value from a range of cells, applying multiple criteria. This function is particularly useful for data analysis, making it easier to filter and identify values that meet specified conditions. In this article, we will cover the syntax, provide several practical examples, and discuss error handling to ensure effective usage of the function.

Syntax

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

• min_range: This is the range of cells from which the minimum value will be determined.
• criteria_range1: This defines the first range of cells that will be evaluated against the criteria.
• criteria1: The condition applied to the first criteria_range to determine which cells are considered.
• [criteria_range2, criteria2]: Optional. Additional ranges and conditions that can be added to further refine the search.

Example #1

=MINIFS(A1:A10, B1:B10, "Apple")

This function returns the minimum value from the range A1:A10, where the corresponding cells in B1:B10 contain the text “Apple.” For instance, if A1:A10 contains values [5, 2, 8] and B1:B10 contains [“Apple”, “Banana”, “Apple”], the result would be 5, as it is the minimum for the entries marked “Apple.”

Example #2

=MINIFS(D2:D10, C2:C10, ">10", C2:C10, "<20")

This function retrieves the lowest value from D2:D10, considering only those entries in C2:C10 that are greater than 10 and less than 20. If, for example, D2:D10 has values [12, 15, 18] and C2:C10 has [9, 15, 25], the output would return an error since no values satisfy both criteria.

Example #3

=MINIFS(F1:F10, G1:G10, "2023-10-01")

Here, the function looks for the minimum in F1:F10 where G1:G10 cells match the date "2023-10-01." If F1:F10 contains values [10, 5, 3] and G1:G10 contains dates with the specified date, the result will be 3, which is the minimum corresponding to that date.

Error handling

• VALUE! - This error occurs if the criteria range and the minimum value range do not have the same dimensions or if any provided criteria is invalid.
• NAME? - This error indicates that Excel does not recognize the function name, often due to a misspelling.
• NUM! - This error happens when criteria-based criteria result in no matched records, thus rendering the minimum value operation impossible.

Conclusion

In summary, the MINIFS function is a valuable resource for users needing to find the minimum value while applying one or more conditions. Understanding its syntax, utilizing practical examples, and being aware of common errors can significantly enhance the efficiency of data analysis in Excel. By mastering this function, users can streamline their data processing tasks and make more informed decisions based on their findings.